SQL

Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” ? Not a big deal, check a workaround!

Problem

Get “ORA-56901: non-constant expression is not allowed for pivot|unpivot values” while trying to execute SQL statement with pivot clause. Lets consider this tiny sample:

  • Here is the table:

 

  • SQL statement with PIVOT clause and constant expressions, which executes successfully:
  • SQL statement with PIVOT clause and dynamic expressions, which fails:
Solution
  • Workaround – just don’t use PIVOT at this point, simple group by clause will fullfill requirement:

 

  • Here is the .sql file with presented content to download.
Oracle DB version 11.2.0.4.0 

Leave a Reply

Your email address will not be published.