[Python] Duckdb unnesting a column's values into unique columns based on a single value from...

I am looking at taking a unnested json blob by unnest(json key, recursive := TRUE), to form a table that looks like this

Column A Column B Column C
Value 1 Val 1 Val 1
Value 2 Val 2 Val 2
Value 3 Val 3 Val 3
Value 4 Val 4 Val 4
Value 5 Val 5 Val 5

The goal I would like to achieve is a single id/row from the multiple rows making up the single id/object state above

Column A 1 Column A 2 Column A 3 Column A 4 Column A 5
Value 1 Val 2 Val 3 Val 4 Val 5

The above would also be repeated horizontal for col B and Col C just didn't have the width.

I am working in duckdb, have tried pivoting the column A however that created columns of the val 1-5 with a 0 or 1 boolean value.

Another other ideas or direction would be appreciated.

Continue reading...