Hello,
I want to use the function crosstab or pivot in a sql query in ODC, but both functions are not recognized. I followed the link from the OS documentation to the official PostgreSQL documentation and used an example of the crosstab(text source_sql, text category_sql) function, but i get the following error:
Unable to execute query. ERROR: function crosstab(text, text) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Has someone a solution how i can use this in ODC
Hi Jan,
Amazon Aurora PostgreSQL database (as used in ODC) does not support PIVOT and UNPIVOT relational operators. Amazon has documented how you can rewrite the T-SQL PIVOT and UNPIVOT here, without the use of crosstab.
https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.pivot.html#chap-sql-server-aurora-pg.tsql.pivot.pg
There is also some more information on how to use crosstab, maybe it helps:https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/
I will ask the ODC team for some feedback.
Regards,
Daniel