Pivot and Unpivot

These keyword was introduced in Oracle 11g.
The UNPIVOT operator transforms the data arranged on a column into separate rows. With this utility it is possible to convert the display of the data to have them displayed on columns to have them on lines. In this example we talk about the prices associated with a ticket:
UNPIVOT :

SELECT * FROM tickets ;

Applying the unpivot: pivoting on the ticket column it is possible to see the opening and closing price on separate lines.

SELECT ticket,to_char(ticket_date,'YYYYMMDD')ticket_date,price_type,price
FROM tickets
UNPIVOT ( price
FOR price_type
IN ( opening_price AS 'OPEN',
closing_price AS 'CLOSE') ) ;
Script:
drop table tickets purge
/
CREATE TABLE tickets (
ticket VARCHAR2 (20),
ticket_date DATE,
opening_price NUMBER,
closing_price NUMBER)
/
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘ABC214’,SYSDATE, 20, 229);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘CDE215’,SYSDATE-12, 100, 200);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘EFG215’,SYSDATE-200, 110, 121);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘GHM216’,SYSDATE-200, 100, 103);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘MNO216’,SYSDATE-201, 122, 11);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘OPQ217’,SYSDATE-1, 49, 46);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘QRS217’,SYSDATE-4, 55, 23);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘STU218’,SYSDATE, 21, 78);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘UVZ218’,SYSDATE-56, 23, 1);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘ZZZ219’,SYSDATE-55, 80, 38);
COMMIT
/
PIVOT on the other hand allows you to transform the display of data based on individual aggregate rows into a single row. In this example we talk about a stadium:
select ring,sector,qty from stadium ;

The ring field shows the various sectors present within it. Applying the PIVOT it is possible to have data of the ring field aggregating the quantity of the single sectors, the transformation allows to obtain the data in a single line.

SELECT ring, SUM(DECODE(sector, ‘red’, qty, 0)) AS red_sum_quantity,
SUM(DECODE(sector, ‘green’, qty, 0)) AS green_sum_quantity,
SUM(DECODE(sector, ‘yellow’, qty, 0)) AS yellow_sum_quantity,
SUM(DECODE(sector, ‘blue’, qty, 0)) AS blue_sum_quantity
FROM stadium
GROUP BY ring ORDER BY ring ;
To create a result set based on pivot we can turn on ring columns then the rows provide a summary for every single sector, as said the keyword was introduced in Oracle 11g.
SELECT *
FROM (SELECT ring, sector, qty
FROM stadium)
PIVOT (SUM(qty) AS sum_sector FOR (sector) IN (‘red’ AS red, ‘green’ AS green, ‘yellow’ AS yellow, ‘blue’ AS blue)) ;
We can remove the ring dimension to have the sum for every single sector :

To show the result in transposes method:
SELECT *
FROM (SELECT sector, qty
FROM stadium)
PIVOT (SUM(qty) AS sum_sector FOR (sector) IN (‘red’ AS red, ‘green’ AS green, ‘yellow’ AS yellow, ‘blue’ AS blue)) ;
The same result we can get with out pivot option too:
SELECT SUM(DECODE(sector, ‘red’, qty, 0)) AS red_sum_quantity,
SUM(DECODE(sector, ‘green’, qty, 0)) AS green_sum_quantity,
SUM(DECODE(sector, ‘yellow’, qty, 0)) AS yellow_sum_quantity,
SUM(DECODE(sector, ‘blue’, qty, 0)) AS blue_sum_quantity
FROM stadium ORDER BY ring ;