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