2
0
PIVOT or UNPIVOT the table data:
The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.
PIVOT can be used to transform a narrow table (e.g. empid, month, sales) into a wider table
(e.g. empid, jan_sales, feb_sales, mar_sales).
• First Create a table with multiple Rows:
create or replace table Xerox_Cust(cust_id varchar2(10), category varchar2(30), Amount
integer)
as select * from values
('100','Printer','3500'),
('100','Printer','300'),
('100','Ink','300'),
('100','Ink','200'),
('100','Cartridge','200'),
('200','Printer','5500'),
('200','Printer','6500'),
('200','Ink','1300'),
('200','Cartridge','1200'),
('300','Printer','2500'),
('300','Ink','2300'),
('300','Cartridge','200'),
('400','Printer','2500'),
('400','Ink','2300'),
('400','Ink','200'),
('500','Printer','2500'),
('500','Printer','2500'),
('500','Ink','2300'); Select * from Xerox_Cust;
Convert the Rows into Columns using PIVOT function:
select *
from Xerox_Cust
pivot(sum(amount) for category in ('Printer', 'Ink', 'Cartridge'))
as p (cust_id,Printer,Ink,Cartridge)
order by cust_id;
UNPIVOT:
Rotates a table by transforming columns into rows. UNPIVOT is a relational operator that
accepts two columns (from a table or subquery), along with a list of columns, and generates a
row for each column specified in the list. In a query, it is specified in the FROM clause after the
table name or subquery.
Note that UNPIVOT is not exactly the reverse of PIVOT as it cannot undo aggregations
made by PIVOT.
• Now if we need to UNPIVOT the above output:
create or replace table xerox_unpivot as
select * from Xerox_Cust
pivot(sum(amount) for category in ('Printer', 'Ink', 'Cartridge'))
as p (cust_id,Printer,Ink,Cartridge)
order by cust_id;
Use the UNPIVOT Function to convert ROWS into Columns:
select * from xerox_unpivot
unpivot(amount for Category in (Printer, Ink, Cartridge))
order by cust_id;
Output : We have the output converted into the ROWS but note here it has not
reverted the aggregations done by PIVOT function, For CUST_ID = 100 Printer
accumulated cost is 3800 while the original table holds two entries 3500 and 300
respectively and PIVOT has aggregated the value (sum(Amount)
