PostgreSQL – Dynamic Pivoting in PostgreSQL

pivotpostgresqlpostgresql-9.3

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

Best Answer

_{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é

Related Solutions

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Postgres Performance Issue – Troubleshooting Intermittent Problems (Docker)

I think this is the answer. I'll update/remove this if I get any contra-indications.

In our (admittedly bizarre) use case, the tables in this ephemeral database are being loaded via COPY FROM statements. Then we start performing transforms.

My hypothesis is that the table statistics do not reflect the actual data population when the queries are run, causing the generation of a poor query plan.

I have forced the execution of an ANALYZE statement after the bulk load, but before the queries began. This seems to have addressed the issue.

Best Answer

Related Solutions

Mysql – Pivot table with MySQL

Postgres Performance Issue – Troubleshooting Intermittent Problems (Docker)

Related Question