Pivot , Unpivot Queries

In the FOR IN clause of PIVOT keyword, we cannot use column names , can only use values 

ORA-56901 : non -constant expression is not allowed in pivot|unpivot values 

 In PIVOT keyword, it is required to provide an aggregate function like COUNT, SUM, AVG,MAX,MIN etc 

ORA-56902 : expect aggregate functions inside pivot operation  

In PIVOT keyword, instead of aggregate function window keyword cannot be used 

ORA-30483 : window functions are not allowed here

Query to Pivot Rows to columns even if aggregate function is not be used (use Max/min with row_number)

SELECT * FROM (
     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));


In HR Schema 

SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name ='EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name,column_id));

 ORA-01790 expression must have same datatype as corresponding expression 

In unpivot function FOR IN clause data-type with same columns are only allowed . In the above query table_name,column_name are varchar2 but column_id is number .So this results in the above error


SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

SELECT * FROM ( SELECT column_name,table_name,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (column_name));


SELECT * FROM ( SELECT column_name,TABLE_NAME FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

Try this query for getting more detail about pivot function

SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(rr) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));
    
 SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  column_name IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));

If there are duplicate records in a query then usage of pivot will removes the duplicate records .For example refer below query


SELECT * FROM (     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'
     union all
      SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'   ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));



 For more examples