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.
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)
);
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;
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 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.
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
)
);
I hope this article will help you.