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
but that’s not a solution for the required problem,if we have to use Decode,why we should go for PIVOT,Is there any workaround with PIVOT to resolve the Problem?
Hi Pratik,
exactly, thats just a workaround to fulfill a requirement to pivot data using non constant expression for pivoting axis. As it is a clear and legal limitation of PIVOT function, I just don’t want to cheat it. Please share if you discover how to achieve this in some way using PIVOT function. Thanks!
Good Luck!