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

DBAORA

oracle expert presents