How would you do dynamic pivot SQL in Amazon Redshift? (This elegant script might be used in many other databases as well)
Several days ago I have spent a few hours of my life figuring out how to do dynamic pivot in Amazon Redshift. To tell you the truth, I have expected much more from this DBMS SQL language. Redshift is based on Postgre SQL 8.0.2 ( which was released in 2005 !!!! ) Anything you would want for this, not too difficult task, does not exits. No stored procedures. No JSON datatype. No variables outside of UDF, no queries inside UDFs. “UDF can be used to calculate values but cannot be used to call SQL functions”. Python UDFs also cannot query the data, only perform calculations. Finally I have found one useful function LISTAGG that helped me to get distinct values of all pivoted columns. For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string. LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ] As