Pivot and Unpivot Operators in Oracle 11g

PIVOT

In this article you will learn how to use PIVOT and UNPIVOT operators. Basically, pivot operator allow you to transpose rows into columns. Prior to 11g, it can be done with DECODE and aggregate functions.

pivot

Let’s start with creating a simple table.

CREATE TABLE T_PIVOT
(
ID NUMBER,
PRODUCT_TYPE VARCHAR2(50),
PRODUCT_NAME VARCHAR2(50),
PRODUCT_PRICE NUMBER,
PRODUCT_STOCK NUMBER
);

INSERT INTO T_PIVOT VALUES(1, 'Keyboard', 'Keyboard-A', 20, 5);
INSERT INTO T_PIVOT VALUES(2, 'Keyboard', 'Keyboard-B', 25, 8);
INSERT INTO T_PIVOT VALUES(3, 'Keyboard', 'Keyboard-C', 15, 2);
INSERT INTO T_PIVOT VALUES(4, 'Keyboard', 'Keyboard-D', 10, 50);
INSERT INTO T_PIVOT VALUES(5, 'Keyboard', 'Keyboard-E', 12, 5);
INSERT INTO T_PIVOT VALUES(6, 'Monitor', 'Monitor-A', 150, 10);
INSERT INTO T_PIVOT VALUES(7, 'Monitor', 'Monitor-B', 135, 15);
INSERT INTO T_PIVOT VALUES(8, 'Monitor', 'Monitor-C', 120, 8);
INSERT INTO T_PIVOT VALUES(9, 'Mouse', 'Mouse-A', 5, 7);
INSERT INTO T_PIVOT VALUES(10, 'Mouse', 'Mouse-B', 10, 15);
INSERT INTO T_PIVOT VALUES(11, 'Mouse', 'Mouse-C', 8, 9);
INSERT INTO T_PIVOT VALUES(12, 'Mouse', 'Mouse-D', 12, 6);
INSERT INTO T_PIVOT VALUES(13, 'Mouse', 'Mouse-E', 15, 7);
INSERT INTO T_PIVOT VALUES(14, 'Mouse', 'Mouse-F', 5, 10);
INSERT INTO T_PIVOT VALUES(15, 'Mouse', 'Mouse-G', 5, 12);
INSERT INTO T_PIVOT VALUES(16, 'Mouse', 'Mouse-H', 15, 15);
INSERT INTO T_PIVOT VALUES(17, 'Mouse', 'Mouse-I', 10, 25);
INSERT INTO T_PIVOT VALUES(18, 'Mouse', 'Mouse-J', 10, 5);
INSERT INTO T_PIVOT VALUES(19, 'Speaker', 'Speaker-A', 25, 5);
INSERT INTO T_PIVOT VALUES(20, 'Speaker', 'Speaker-B', 20, 12);
INSERT INTO T_PIVOT VALUES(21, 'Speaker', 'Speaker-C', 20, 8);
INSERT INTO T_PIVOT VALUES(22, 'Speaker', 'Speaker-D', 25, 6);
INSERT INTO T_PIVOT VALUES(23, 'Speaker', 'Speaker-E', 26, 7);
INSERT INTO T_PIVOT VALUES(24, 'Speaker', 'Speaker-F', 26, 4);
INSERT INTO T_PIVOT VALUES(25, 'USB Drive', 'USB-A', 12, 18);
INSERT INTO T_PIVOT VALUES(26, 'USB Drive', 'USB-B', 10, 8);
INSERT INTO T_PIVOT VALUES(27, 'USB Drive', 'USB-C', 15, 5);
INSERT INTO T_PIVOT VALUES(28, 'Power Supplies', 'Power-A', 35, 25);
INSERT INTO T_PIVOT VALUES(29, 'Storage', 'Storage-A', 50, 8);
INSERT INTO T_PIVOT VALUES(30, 'Storage', 'Storage-B', 56, 6);
INSERT INTO T_PIVOT VALUES(31, 'Storage', 'Storage-C', 65, 16);
INSERT INTO T_PIVOT VALUES(32, 'Storage', 'Storage-D', 54, 6);
INSERT INTO T_PIVOT VALUES(33, 'Storage', 'Storage-E', 45, 6);
INSERT INTO T_PIVOT VALUES(34, 'Storage', 'Storage-F', 60, 12);
INSERT INTO T_PIVOT VALUES(35, 'Storage', 'Storage-G', 60, 10);

COMMIT;

SELECT * FROM (
    SELECT PRODUCT_TYPE PT, SUM(PRODUCT_STOCK) PS FROM T_PIVOT GROUP BY PRODUCT_TYPE
) PIVOT (
    SUM(PS) AS TOTAL_STOCK FOR (PT) IN ('Power Supplies' AS PS, 
                                        'USB Drive' AS USB, 
                                        'Mouse' AS MOUSE, 
                                        'Speaker' AS SPK, 
                                        'Storage' AS STR, 
                                        'Keyboard' AS KEYB, 
                                        'Monitor' AS MON)
);

pivot query output

You can partition off by any column that you want. I added the max_id column as an example.

SELECT * FROM (
    SELECT MAX(ID) MAX_ID, PRODUCT_TYPE PT, SUM(PRODUCT_STOCK) PS FROM T_PIVOT GROUP BY PRODUCT_TYPE
) PIVOT (
    SUM(PS) AS TOTAL_STOCK FOR (PT) IN ('Power Supplies' AS PS, 
                                        'USB Drive' AS USB, 
                                        'Mouse' AS MOUSE, 
                                        'Speaker' AS SPK, 
                                        'Storage' AS STR, 
                                        'Keyboard' AS KEYB, 
                                        'Monitor' AS MON)
) ORDER BY MAX_ID;
pivot query output

UNPIVOT

The UNPIVOT operator performs the opposite action of pivot. In other words, it converts columns into rows

CREATE TABLE T_UNPIVOT
(
ID NUMBER,
PRODUCT_CODE VARCHAR2(50),
PRODUCT_STOCK_A VARCHAR2(50),
PRODUCT_STOCK_B VARCHAR2(50),
PRODUCT_STOCK_C VARCHAR2(50),
PRODUCT_STOCK_D VARCHAR2(50)
);

INSERT INTO T_UNPIVOT VALUES(1, 'A100', 5, 10, 20, 8);
INSERT INTO T_UNPIVOT VALUES(2, 'A200', 25, NULL, NULL, NULL);
INSERT INTO T_UNPIVOT VALUES(3, 'AL50', 16, 5, NULL, NULL);
INSERT INTO T_UNPIVOT VALUES(4, 'RS40', 10, NULL, NULL, 25);
INSERT INTO T_UNPIVOT VALUES(5, 'SP50', NULL, NULL, 20, 8);

COMMIT;
SELECT * FROM T_UNPIVOT 
UNPIVOT (
    PRODUCT_STOCK FOR COL_VAL IN (PRODUCT_STOCK_A, PRODUCT_STOCK_B, PRODUCT_STOCK_C, PRODUCT_STOCK_D)
);

By default the EXCLUDE NULLS clause is used. You can also include null values with INCLUDE NULLS clause as shown below.

SELECT * FROM T_UNPIVOT 
UNPIVOT INCLUDE NULLS (
    PRODUCT_STOCK FOR COL_VAL IN (PRODUCT_STOCK_A, 
                                  PRODUCT_STOCK_B, 
                                  PRODUCT_STOCK_C, 
                                  PRODUCT_STOCK_D)
);
unpivot query output

UNPIVOT MULTIPLE COLUMNS

It is also possible to unpivot multiple column groups. Let’s recreate the T_UNPIVOT table with random data.

DROP TABLE T_UNPIVOT;

CREATE TABLE T_UNPIVOT
AS
SELECT 
LEVEL ID,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_A1,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_A2,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_A3,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_B1,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_B2,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_B3,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_C1,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_C2,
ROUND(DBMS_RANDOM.VALUE(1,100), 2) COL_C3
FROM DUAL CONNECT BY 
LEVEL < 5;

Our table look like the following.

table output
SELECT * FROM T_UNPIVOT 
UNPIVOT (
    (A, B, C) FOR COL_GROUP IN (
                            (COL_A1, COL_B1, COL_C1) AS 1, 
                            (COL_A2, COL_B2, COL_C2) AS 2, 
                            (COL_A3, COL_B3, COL_C3) AS 3
                               )
);   
unpivot multiple columns output

I hope this article will help you.

Posted in SQL
Leave a Reply

Your email address will not be published.