Hi All,
I tried PIVOT in sql query. It shows the below error.
ORA-56901: non-constant expression is not allowed for pivot|unpivot values
56901. 0000 - "non-constant expression is not allowed for pivot|unpivot values"
*Cause: Attempted to use non-constant expression for pivot|unpivot values.
*Action: Use constants for pivot|unpivot values.
Error at Line: 30 Column: 24
SQL Query:
WITH data_to_pivot AS
(
SELECT 'Invest Division' as org_info, 'Eligibility Invest Division' as department, 'Game_Test' project_name, 40 as total_quantity FROM DUAL
UNION
SELECT 'Invest Division' as org_info, 'Eligibility Invest Division' as department, 'Land_Test' project_name, 15 as total_quantity FROM DUAL
UNION
SELECT 'Invest Division' as org_info, 'Eligibility Invest Division' as department, 'Land_Test' project_name, 12 as total_quantity FROM DUAL
UNION
SELECT 'Invest Division' as org_info, 'Invest Division' as department, 'Game_Test' project_name, 12 as total_quantity FROM DUAL
UNION
SELECT 'Invest Division' as org_info, 'Invest Division' as department, 'Game_Test' project_name, 40 as total_quantity FROM DUAL
UNION
SELECT 'Invest Division' as org_info, 'Invest Division' as department, 'Land_Test' project_name, 12 as total_quantity FROM DUAL
UNION
SELECT 'Operations Division' as org_info, 'Financial Invest Division' as department, 'Game_Test' project_name, 8 as total_quantity FROM DUAL
UNION
SELECT 'Operations Division' as org_info, 'Financial Invest Division' as department, 'Land_Test' project_name, 8 as total_quantity FROM DUAL
UNION
SELECT 'Operations Division' as org_info, 'Regulatory Assurance' as department, 'Game_Test' project_name, 10 as total_quantity FROM DUAL
UNION
SELECT 'Operations Division' as org_info, 'Regulatory Assurance' as department, 'Land_Test' project_name, 10 as total_quantity FROM DUAL
)SELECT org_info
, department
, project_name
, total_quantity
FROM data_to_pivot
PIVOT ( sum (total_quantity)
FOR project_name IN ( org_info AS org_info
, department as department
)
)
ORDER BY org_info
Output :
Project\_name
Game\_Test Land\_Test
Org_info Department
Invest Division Eligibility Invest Division 40 27
Invest Division Invest Division 52 12
Operations Division Financial Invest Division 8 8
Operations Division Regulatory Assurance 10 10
Please do suggest to achieve this output.
Thanks
Kavitha