PIVOT UNPIVOT statements 11g

This article presents how to use new operators PIVOT, UNPIVOT in Oracle 11g

  • PIVOT is used to write cross-tabulation queries that rotate column values into new columns and aggregates data. It increases number of columns :).

PIVOTING quarter

product country quarter amount
jeans poland Q1 10
jeans us Q2 20
jeans germany Q3 30
shorts japan Q1 10
shorts poland Q1 15
shorts us Q2 20
product Q1 Q2 Q3
jeans 10 20 30
shorts 25 20

Clause syntax. It looks complicated but soon it will be clear.

table_reference PIVOT[XML]
( aggregate_function( expr ) [[AS] alias]
  [, aggregate_function( expr ) [[AS] alias] ] ...
  pivot_for_clause
  pivot_in_clause )

--list column(s) to pivot -> values of the column(s) 
--will be pivoted to columns
pivot_for_clause =
FOR {column |{ column [,column]...) }

pivot_in_clause =
IN ( {{ { expr | ( expr [, expr] ...) } [[AS] alias] } ...
   | subquery | { ANY | ANY [,ANY]... }} )

Prepare data for test PIVOT

DROP TABLE test_tbl;
 
CREATE TABLE test_tbl (
  product  VARCHAR2(30),
  country  VARCHAR2(30),
  quarter  VARCHAR2(2),
  amount   NUMBER
);
 
INSERT INTO test_tbl VALUES('jeans',  'poland',  'Q1', 10);
INSERT INTO test_tbl VALUES('jeans',  'us',      'Q2', 20);
INSERT INTO test_tbl VALUES('jeans',  'germany', 'Q3', 30);
INSERT INTO test_tbl VALUES('shorts', 'japan',   'Q1', 10);
INSERT INTO test_tbl VALUES('shorts', 'poland',  'Q1', 15);
INSERT INTO test_tbl VALUES('shorts', 'us',      'Q2', 20);
 
COMMIT;
 
SELECT * FROM test_tbl;
PRODUCT    COUNTRY                  QUARTER AMOUNT
---------- ------------------------ ------- ------
jeans      poland                   Q1          10 
jeans      us                       Q2          20 
jeans      germany                  Q3          30 
shorts     japan                    Q1          10 
shorts     poland                   Q1          15 
shorts     us                       Q2          20
Following query pivots values(Q1,Q2,Q3) of column quarter.
SELECT *
FROM  
(SELECT product, quarter, amount FROM test_tbl) --table_reference
pivot
(SUM(amount) AS alias_amt    --aggregate_function
 FOR (quarter)               --pivot_for_clause 
 IN ('Q1', 'Q2', 'Q3')       --pivot_in_clause
);
 
PRODUCT          'Q1'_ALIAS_AMT 'Q2'_ALIAS_AMT 'Q3'_ALIAS_AMT
---------------- -------------- -------------- --------------
jeans            10             20             30 
shorts           25             20
table_reference – source of data for PIVOT – product, quarter, amount
aggregate_function – aggregation function “sum(amount)” with alias “alias_amt
pivot_for_clause – rotate column – quarter
pivot_in_clause – hard coded values of rotate column – Q1,Q2,Q3
All hard coded values of pivot_in_clause(Q1,Q2,Q3) and all aliases in aggregate_function(ALIAS_AMT) define PIVOT columns:
‘Q1’_ALIAS_AMT
‘Q2’_ALIAS_AMT
‘Q3’_ALIAS_AMT

so together product,’Q1’_ALIAS_AMT,’Q2’_ALIAS_AMT,’Q3’_ALIAS_AMT are used to group data and generate aggregation function sum(amount)

To get the same result in 10g you would need to write following query

SELECT 
  product, 
  SUM(DECODE(quarter, 'Q1', amount)) Q1_ALIAS_AMT,
  SUM(DECODE(quarter, 'Q2', amount)) Q2_ALIAS_AMT,
  SUM(DECODE(quarter, 'Q3', amount)) Q3_ALIAS_AMT
FROM test_tbl
GROUP BY product;
 
PRODUCT    Q1_ALIAS_AMT Q2_ALIAS_AMT Q3_ALIAS_AMT
---------- ------------ ------------ ------------
jeans                10           20           30 
shorts               25           20

Extra column: country

Our data is grouped now by 2 columns “country” and “product” so we have more result sets

SELECT *
FROM  
(SELECT country,  product, quarter, amount 
 FROM test_tbl)          --table_reference
pivot
(SUM(amount) AS amount   --aggregate_function
 FOR (quarter)           --pivot_for_clause 
 IN ('Q1', 'Q2', 'Q3')   --pivot_in_clause
);
 
COUNTRY     PRODUCT             'Q1'_AMOUNT 'Q2'_AMOUNT 'Q3'_AMOUNT
----------  ------------------- ----------- ----------- -----------
us          jeans                           20             
us          shorts                          20             
poland      jeans               10                         
japan       shorts              10                         
germany     jeans                                        30 
poland      shorts                          15

Extra function: sum(amount/2) b

Because all hard coded values of pivot_in_clause(Q1,Q2,Q3) and all aliases in aggregate_function(a, b) define PIVOT columns so now we have 6 new columns:

‘Q1’_A

‘Q2’_A

‘Q3’_A

‘Q1’_B

‘Q2’_B

‘Q3’_B

SELECT *
FROM  
(SELECT product, quarter, amount FROM test_tbl) --table_reference
pivot
(SUM(amount) a, SUM(amount/2) b   --aggregate_function
 FOR (quarter)           --pivot_for_clause 
 IN ('Q1', 'Q2', 'Q3')   --pivot_in_clause
);
 
PRODUCT               'Q1'_A 'Q1'_B 'Q2'_A 'Q2'_B 'Q3'_A 'Q3'_B
--------------------- ------ ------ ------ ------ ------ ------
jeans                 10      5     20     10     30     15 
shorts                25   12,5     20     10

Extra pivot column: product

This example shows that many columns can be used to PIVOT values

SELECT *
FROM  
(SELECT country, product, quarter, amount 
 FROM test_tbl)          --table_reference
pivot
(SUM(amount) AS amount   --aggregate_function
 FOR (product, quarter)  --pivot_for_clause 
 IN (('jeans','Q1'), ('shorts','Q1'))       --pivot_in_clause
);
 
COUNTRY              'jeans'_'Q1'_AMOUNT 'shorts'_'Q1'_AMOUNT
-------------------- ------------------- --------------------
japan                10 
us                               
germany                       
poland               10                   15

Subquery in pivot_in_cluase and PIVOT XML

If you want to generate xml data you can use clause PIVOT XML. It requires to specify subquery for pivot_in_cluase, hardcoded values are not allowed here. Subquery is very interesting option however you can’t use it for PIVOT caluse but only for PIVOT XML.

select *
from  
(select product, quarter, amount 
 from test_tbl)                    --table_reference
PIVOT XML
(sum(amount) as amount             --aggregate_function
 for (quarter)                     --pivot_for_clause 
 in (select quarter from test_tbl) --pivot_in_clause 
                                   --subquery only for XML
);

PRODUCT  QUARTER_XML
-------- ----------------------------------------------
jeans    <PivotSet>
           <item>
             <column name = "QUARTER">Q1</column>
             <column name = "AMOUNT">10</column>
           </item>
           <item>
             <column name = "QUARTER">Q2</column>
             <column name = "AMOUNT">20</column>
           </item>
           <item>
             <column name = "QUARTER">Q3</column>
             <column name = "AMOUNT">30</column>
           </item>
         </PivotSet>
shorts   <PivotSet>
           <item>
             <column name = "QUARTER">Q1</column>
             <column name = "AMOUNT">25</column>
           </item>
           <item>
             <column name = "QUARTER">Q2</column>
             <column name = "AMOUNT">20</column>
           </item>
           <item>
             <column name = "QUARTER">Q3</column>
             <column name = "AMOUNT"></column>
           </item>
         </PivotSet>

ANY and PIVOT XML

Instead using subquery you can specify ANY clause. It’s allowed only with PIVOT XML and can’t be used with PIVOT.

select *
from  
(select product, quarter, amount 
 from test_tbl)                    --table_reference
pivot xml
(sum(amount) as amount             --aggregate_function
 for (quarter)                     --pivot_for_clause 
 in (ANY)                          --pivot_in_clause 
                                   --ANY only for XML
);

PRODUCT  QUARTER_XML
-------- --------------------------------------------------------
jeans    <PivotSet>
           <item>
             <column name = "QUARTER">Q1</column>
             <column name = "AMOUNT">10</column>
           </item>
           <item>
             <column name = "QUARTER">Q2</column>
             <column name = "AMOUNT">20</column>
           </item>
           <item>
             <column name = "QUARTER">Q3</column>
             <column name = "AMOUNT">30</column>
           </item>
         </PivotSet>
shorts   <PivotSet>
           <item>
             <column name = "QUARTER">Q1</column>
             <column name = "AMOUNT">25</column>
           </item>
           <item>
             <column name = "QUARTER">Q2</column>
             <column name = "AMOUNT">20</column>
           </item>
         </PivotSet>
  •  UNPIVOT is used to rotate columns into values of a column. It decreases number of columns :).
product Q1 Q2 Q3
jeans 10 20 30
shorts 25 20
product quarter
jeans Q1
jeans Q2
jeans Q3
shorts Q1
shorts Q1
shorts Q2

Clause syntax

 table_reference UNPIVOT [{INCLUDE|EXCLUDE} NULLS]
-- specify the measure column(s) to be unpivoted
({column|(column[,column]...)}
unpivot_for_clause
unpivot_in_clause )

--specify one or more names for the columns that will
--result from the unpivot operation
unpivot_for_clause =
FOR {column|{column[,column]...)}

--specify the columns that will be unpivoted into values of
--the column specified in the unpivot_for_clause
unpivot_in_clause =
({column|(column[,column]...)}
  [AS {constant|(constant[,constant]...)}]
    [,{column|(column[,column]...)}
      [AS {constant|(constant[,constant]...)}]...)

Prepare data for UNPIVOT

DROP TABLE test_tbl;
 
CREATE TABLE test_tbl (
  country            NUMBER,
  product1_quarter1  NUMBER,
  product2_quarter1  NUMBER,
  product2_quarter2 NUMBER
);
 
INSERT INTO test_tbl VALUES (1, 10, 17, NULL);
INSERT INTO test_tbl VALUES (2, 5,  12, NULL);
INSERT INTO test_tbl VALUES (3, 12,  3, 4);
INSERT INTO test_tbl VALUES (4, 10, 10, 2);
COMMIT;
 
SELECT * FROM test_tbl;
   COUNTRY PRODUCT1_QUARTER1 PRODUCT2_QUARTER1 PRODUCT2_QUARTER2
---------- ----------------- ----------------- -----------------
         1                10                17                   
         2                 5                12                   
         3                12                 3                 4 
         4                10                10                 2

Following query UNPIVOT data

SELECT * FROM test_tbl
unpivot
(quantity FOR product_quarter     --unpivot_for_clause
IN (product1_quarter1 AS 'p1_q1', --unpivot_in_clause
    product2_quarter1 AS 'p2_q1',
    product2_quarter2 AS 'p2_q2')
);
 
   COUNTRY PRODUCT_QUARTER   QUANTITY
---------- --------------- ----------
         1 p1_q1                   10 
         1 p2_q1                   17 
         2 p1_q1                    5 
         2 p2_q1                   12 
         3 p1_q1                   12 
         3 p2_q1                    3 
         3 p2_q2                    4 
         4 p1_q1                   10 
         4 p2_q1                   10 
         4 p2_q2                    2

As default UNPIVOT is executed with EXCLUDE NULLS so nulls are skipped. To include them just add INCLUDE NULLS clause.

SELECT * FROM test_tbl
unpivot include nulls
(quantity FOR product_quarter 
IN (product1_quarter1 AS 'p1_q1', 
    product2_quarter1 AS 'p2_q1',
    product2_quarter2 AS 'p2_q2')
);
 
   COUNTRY PRODUCT_QUARTER   QUANTITY
---------- --------------- ----------
         1 p1_q1                   10 
         1 p2_q1                   17 
         1 p2_q2                      
         2 p1_q1                    5 
         2 p2_q1                   12 
         2 p2_q2                      
         3 p1_q1                   12 
         3 p2_q1                    3 
         3 p2_q2                    4 
         4 p1_q1                   10 
         4 p2_q1                   10 
         4 p2_q2                    2

To create separate product and quarter columns you need to define more detailed unpivot_for_clause, unpivot_in_clause

SELECT * FROM test_tbl
unpivot include nulls
(quantity FOR (quarter, product)      --unpivot_for_clause
IN (product1_quarter1 AS ('q1', 'p1'), --unpivot_in_clause
    product2_quarter1 AS ('q1', 'p2'),
    product2_quarter2 AS ('q2', 'p2'))
);
 
   COUNTRY QUARTER PRODUCT   QUANTITY
---------- ------- ------- ----------
         1 q1      p1              10 
         1 q1      p2              17 
         1 q2      p2                 
         2 q1      p1               5 
         2 q1      p2              12 
         2 q2      p2                 
         3 q1      p1              12 
         3 q1      p2               3 
         3 q2      p2               4 
         4 q1      p1              10 
         4 q1      p2              10 
         4 q2      p2               2

UNPIVOT many columns

In previous examples we were unpivoting one column. In following example I’ll show you how to unpivot many columns.

DROP TABLE test_tbl;
 
CREATE TABLE test_tbl
(cust_id NUMBER,
 jan_inc NUMBER,
 jan_exp NUMBER,
 feb_inc NUMBER,
 feb_exp NUMBER,
 mar_inc NUMBER,
 mar_exp NUMBER);
 
INSERT INTO test_tbl VALUES(1,10,2,12,6,45,33);
INSERT INTO test_tbl VALUES(2,10,2,12,6,45,33);
INSERT INTO test_tbl VALUES(3,10,2,12,6,45,33);
 
COMMIT;
 
SELECT * FROM test_tbl;
 
   CUST_ID JAN_INC JAN_EXP FEB_INC FEB_EXP MAR_INC MAR_EXP
---------- ------- ------- ------- ------- ------- -------
         1      10       2      12       6      45      33 
         2      10       2      12       6      45      33 
         3      10       2      12       6      45      33

Following query unpivot groups of columns as month

SELECT * FROM test_tbl
unpivot
((income, expense) FOR (MONTH)
 IN ((jan_inc, jan_exp) AS 'January',
     (feb_inc, feb_exp) AS 'February',
     (mar_inc, mar_exp) AS 'March')
);
 
   CUST_ID MONTH        INCOME    EXPENSE
---------- -------- ---------- ----------
         1 January          10          2 
         1 February         12          6 
         1 March            45         33 
         2 January          10          2 
         2 February         12          6 
         2 March            45         33 
         3 January          10          2 
         3 February         12          6 
         3 March            45         33

Have a fun 🙂

Tomasz

 

Leave a Reply

Your email address will not be published. Required fields are marked *