Format of Pivot
Select
From
(Inner_query)
Pivot (aggreate_function for pivot_column in (list of values))
Order BY ...;
Examples of usage:
Select
From (
Select Month,prd_type_id,amount
From All_sales
)
Pivot (sum (amount) for month in (1 as jan,2 as feb,3 as mar,4 as APR)
)
ORDER BY prd_type_id
Convert multiple columns
SELECT * FROM
(Select Month,prd_type_id,amount
From All_sales
)
Pivot (sum (amount) for (month,prd_type_id) in (
As JAN_P2, (2,3) as FEB_P3)
);
Using multiple aggregate functions in a transformation
SELECT * FROM (select Cust_no,mag_man_cert_type,t.mag_man_cert_no,mag_man_type from L_cif_ent_cust_mag_man_info t
Pivot (Max (mag_man_cert_no) as NO, Max (Mag_man_cert_type) as type for mag_man_type in (' GLR01 ' as ' GLR02 ' GLR03));
Unpivot can be used for a column change, the field type of the column must be the same
Examples of UNPIVOT usage:
SELECT * FROM Pivot_sales_date
Unpivot (Amount for month in (JAN,FEB,MAR,APR));
Use of Oracle pivot and UNPIVOT functions