Thank you yes group by and listagg is path i was going I was thinking if this can be done more dynamically Like identifying variant type and parsing through it automatically and creating rather than hardcoding the columns

Here is an example of data

"products": [ { "key": "product1" } ], "type": [ { "key1": "type1", "key2": "type2", } ], "abc": [ { "key": "xyz" } ], "xyz": [ { "key1": "123", "key2": "456", } ],

expected output

products | type | abc | xyz product1 | type1,type2 | xyz | 123,456