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;
SQL> select * from test_unpivot;
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.