Dynamic pivoting in PostgreSQL

Question

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:

Desired Ouput:

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é

---