Pivot and unpivot operator use in SQL query of Oracle

PIVOT
PIVOT operator(row to column) take data of separate rows in a table, aggregates the data and available output into columns format.

Example of using Pivot in SQL Queries

1. Following is the table used as example of showing pivot queries.

SQL> select * from test;

        ID    CUST_ID    ITEM_ID   QUANTITY
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          2          2
         3          1          3          1
         4          1          3          1
         5          2          3          1
         6          3          3          1

6 rows selected.

2. Show the sum of Quantity according to the item id in table.

SQL> select * from (select item_id,quantity from test) pivot (sum(quantity) as sum_quantity for (item_id) in (1,2,3));

1_SUM_QUANTITY 2_SUM_QUANTITY 3_SUM_QUANTITY
-------------- -------------- --------------
             1              2              4

3. We can add Customer id with pivot sum of quantity of the item id in table.

SQL> select * from (select cust_id,item_id,quantity from test) pivot (sum(quantity) as sum_quantity for (item_id) in (1,2,3));

   CUST_ID 1_SUM_QUANTITY 2_SUM_QUANTITY 3_SUM_QUANTITY
---------- -------------- -------------- --------------
         1              1                             2
         2                             2              1
         3                                            1

UNPIVOT
UNPIVOT operator(columns to rows) converts column-based data into separate rows.

1. Use the following table to show unpivot queries in SQL.

create table test_unpivot ( cust_id number, Quantity1 number ,quantity2 number ,Quantity3 number);

insert into test_unpivot values (1,1,null,2);
insert into test_unpivot values (2,null,2,1);
insert into test_unpivot values (3,null,null,1);
commit;

   CUST_ID  QUANTITY1  QUANTITY2  QUANTITY3
---------- ---------- ---------- ----------
         1          1                     2
         2                     2          1
         3                                1

2. Following query convert the column 1,2,3 quantity item_code from column to row based.

SELECT *
FROM test_unpivot
UNPIVOT (quantity FOR item_code IN (QUANTITY1 AS '1', QUANTITY2 AS '2', QUANTITY3 AS '3'));

   CUST_ID I   QUANTITY
---------- - ----------
         1 1          1
         1 3          2
         2 2          2
         2 3          1
         3 3          1

3. If you want to include the nulls rows while converting column to row data using unpivot option.

SELECT *
FROM test_unpivot
UNPIVOT INCLUDE NULLS (quantity FOR item_code IN (QUANTITY1 AS '1', QUANTITY2 AS '2', QUANTITY3 AS '3'));

   CUST_ID I   QUANTITY
---------- - ----------
         1 1          1
         1 2
         1 3          2
         2 1
         2 2          2
         2 3          1
         3 1
         3 2
         3 3          1

9 rows selected.