I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, along with its product_id (that too in columns), but we cannot predict how many products will there for a particular order_number.

Attaching a sample input table and desired output. I have tried the crosstab function but the result is not correct.

Table values: enter image description here

Desired Ouput:

enter image description here


1 Answer 1


Contributions originally left as comments

I typically aggregate dynamic columns into a single JSON value these days rather than creating dynamic columns for each attribute. That might be a challenge though as well, with your outdated (and unsupported) Postgres version - a-horse-with-no-name

The desired output can't be obtained directly in SQL. You need to write a query that generates the query that generates your desired output. Giving up on your desired output and producing JSON instead may also be considered, as it would be simpler. - Daniel Vérité

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.