Example of Pivot and unpivot operator use in SQL query of Oracle

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.