Pivot and Unpivot – AXIANS DB

This article shows how to use the PIVOT and UNPIVOT operators in Oracle 11g database, the keyword was introduced in that Oracle version. Before that you can get it by DECODE or CASE functions .

UNPIVOT operator

That operator transforms the data arranged on a column into separate rows.

With this utility it is possible to convert the display of the data originally displayed on columns to have them on lines. In this example we talk about the prices associated with a ticket:

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') 
)  
ORDER BY ticket,price_type ;

The columns PRICE and PRICE_TYPE, are defined in the UNPIVOT clause
The columns to be unpivoted must be named in the IN clause
By default, the clause of « EXCLUDE NULLS » is used. To override the default behaviour, use the INCLUDE NULLS clause.

SELECT ticket,to_char(ticket_date,'YYYYMMDD')ticket_date,price_type,price 
FROM tickets 
UNPIVOT INCLUDE NULLS ( price 
  FOR price_type 
  IN (  opening_price AS 'OPEN', 
        closing_price AS 'CLOSE') 
) 
ORDER BY ticket,price_type ;

Here is the code if you want to test :

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); 
INSERT INTO tickets (ticket, ticket_date, opening_price, closing_price) VALUES ('CASE999',SYSDATE-21, 23, null); 
INSERT INTO tickets (ticket, ticket_date, opening_price, closing_price) VALUES ('CASE998',SYSDATE-19, null,80); 
 
COMMIT 
/

PIVOT operator

This one on the other side allows you to transform the display of data based on multiple rows into a single row. In this example we talk about a stadium:

SELECT ring,sector,qty FROM stadium ORDER BY ring, sector ;

In 10g, you can get by with the DECODE or CASE functions, to know the number of seats on stadium, consider aggregating the data .

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 aggregate data without the column ring :

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 ;

As of 11G, the data aggregated can be proposed by PIVOT operator :

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))  
;

Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format.

SELECT * 
FROM ( 
  SELECT sector, qty FROM stadium) 
PIVOT XML(SUM(qty) AS sum_sector FOR (sector) IN (SELECT DISTINCT sector FROM stadium)) ;

The script to reproduce :

CREATE TABLE stadium ( 
ring VARCHAR2 (20), 
sector VARCHAR2 (20), 
qty NUMBER 
) 
/ 
-- 1 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('1','red',100) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('1','green',300) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('1','yellow',300) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('1','blue',200) ; 
-- 2 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('2','red',50) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('2','green',400) ; 
INTO STADIUM(ring,sector,qty) VALUES ('2','yellow',400) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('2','blue',300) ; 
-- 3 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('3','red',10) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('3','green',50) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('3','yellow',50) ; 
INSERT INTO STADIUM(ring,sector,qty) VALUES ('3','blue',30) ; 
COMMIT 
/