Maria Zakourdaev is not Redshift’s biggest fan:
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.
Read on to see how Maria solved this problem. And to tell the truth, I’m not Redshift’s biggest fan either.