PIVOT and UNPIVOT in Oracle

Pivot function works similar to transpose function in Microsoft Excel. It can convert rows into columns giving aggregated results.

PIVOT function is available in and above versions Oracle 11g and Oracle 12c.

PIVOT

Syntax

SELECT * FROM
(
 SELECT <columnname1>, <columnname2>
 FROM <tablename>
 WHERE <condition>
)
PIVOT 
(
 aggregate_function(<columnname2>)
 FOR <columnname2>
 IN ( <expression_1>, <expression_2>, ... <expression_n>) | subquery
)
ORDER BY <expression> [ ASC | DESC ];

Here,

columnname1, columnname2 = Columns of table
condition                                      = joining condition in where clause
aggregate_function                    = SUM, MIN, MAX, AVG, COUNT
expression_1, expression_2     = values of columnname2
subquery                                       = results of subquery can be used as values of columnname2

Let us understand PIVOT by simple example:

Create a table named emp_mast…

CREATE TABLE emp_mast
(
emp_id NUMBER,
emp_code VARCHAR2(10),
emp_name VARCHAR2(100),
salary NUMBER,
dept_no NUMBER,
);

Insert rows into that table and commit…

INSERT INTO emp_mast VALUES (1, 'A1', 'Zed', 1000, 10);
INSERT INTO emp_mast VALUES (2, 'A2', 'Akash', 2000, 10);
INSERT INTO emp_mast VALUES (3, 'A3', 'Badal', 3000, 20);
INSERT INTO emp_mast VALUES (4, 'A4', 'Panchi', 2000,10);
INSERT INTO emp_mast VALUES (5, 'A5', 'Pratik', 4000, 20);
COMMIT;

Using PIVOT in SQL query

SELECT * FROM
(
SELECT dept_no, salary
FROM emp_mast
)
PIVOT
(
SUM(salary) AS total_salary FOR (dept_no) IN (10 AS Ten, 20 AS Twenty)
);

Result

TEN_TOTAL_SALARYTWENTY_TOTAL_SALARY
50007000

If we want breakdown my emp_code then add it in SELECT CLAUSE.

SELECT * FROM
(
SELECT emp_code, dept_no, salary
FROM emp_mast
)
PIVOT
(
SUM(salary) AS total_salary FOR (dept_no) IN (10 AS Ten, 20 AS Twenty)
);

Result

EMP_CODETEN_TOTAL_SALARYTWENTY_TOTAL_SALARY
A22000
A33000
A11000
A54000
A42000

There is a limit in specifying the values in the IN clause of dept_no. It cannot be infinite values

If you want results in XML add XML to PIVOT operator

SELECT * FROM
(
SELECT dept_no, salary
FROM emp_mast
)
PIVOT XML
(
SUM(SALARY) AS total_salary FOR (dept_no) IN (SELECT DISTINCT dept_no FROM emp_mast)
);

Result

DEPT_NO_XML
==============
<PivotSet><item><column name = "DEPT_NO">10</column><column name = "TOTAL_SALARY">5000</column></item><item><column name = "DEPT_NO">20</column><column name = "TOTAL_SALARY">7000</column></item></PivotSet>

To break down XML by emp_id just add a column in the select query…

SELECT * FROM
(
SELECT emp_id, dept_no, salary
FROM emp_mast
)
PIVOT XML
(
SUM(salary) AS total_salary FOR (dept_no) IN (SELECT DISTINCT dept_no FROM emp_mast)
);

Result

EMP_IDDEPT_NO_XML
1<PivotSet><item><column name = “DEPT_NO”>10</column><column name = “TOTAL_SALARY”>1000</column></item><item><column name = “DEPT_NO”>20</column><column name = “TOTAL_SALARY”></column></item></PivotSet>
2<PivotSet><item><column name = “DEPT_NO”>10</column><column name = “TOTAL_SALARY”>2000</column></item><item><column name = “DEPT_NO”>20</column><column name = “TOTAL_SALARY”></column></item></PivotSet>
3<PivotSet><item><column name = “DEPT_NO”>10</column><column name = “TOTAL_SALARY”></column></item><item><column name = “DEPT_NO”>20</column><column name = “TOTAL_SALARY”>3000</column></item></PivotSet>
4<PivotSet><item><column name = “DEPT_NO”>10</column><column name = “TOTAL_SALARY”>2000</column></item><item><column name = “DEPT_NO”>20</column><column name = “TOTAL_SALARY”></column></item></PivotSet>
5<PivotSet><item><column name = “DEPT_NO”>10</column><column name = “TOTAL_SALARY”></column></item><item><column name = “DEPT_NO”>20</column><column name = “TOTAL_SALARY”>4000</column></item></PivotSet>

UNPIVOT

Unpivot function transposes column data into rows.

Let us understand it by example

Create a table emp_mast_unpivot…

CREATE TABLE emp_mast_unpivot
(
emp_id NUMBER,
emp_code varchar2(10),
dept_no_10_sal NUMBER,
dept_no_20_sal NUMBER,
dept_no_30_sal NUMBER
);

Insert data into the table…

INSERT INTO emp_mast_unpivot VALUES (1, 'A1', 1000, null, null);
INSERT INTO emp_mast_unpivot VALUES (1, 'A2', 3000, null, null);
INSERT INTO emp_mast_unpivot VALUES (1, 'A3', null, 3000, null);
INSERT INTO emp_mast_unpivot VALUES (1, 'A4', null, null, 4000);
INSERT INTO emp_mast_unpivot VALUES (1, 'A5', 2000, null, 4000);
COMMIT;

Using UNPIVOT clause in the query will convert data of columns into rows. In this case, we will get information about an employee, his department code and salary he gets.

SELECT * 
FROM emp_mast_unpivot 
UNPIVOT 
( 
sal FOR dept_no IN ( dept_no_10_sal, dept_no_20_sal, dept_no_30_sal) 
);

Result

EMP_IDEMP_CODEDEPT_NOSAL
1A1DEPT_NO_10_SAL1000
2A2DEPT_NO_10_SAL3000
3A3DEPT_NO_20_SAL3000
4A4DEPT_NO_30_SAL4000
5A5DEPT_NO_10_SAL2000

Include Nulls

By default, EXCLUDE NULLS is used to display data that does not contain null values.

We can use INCLUDE NULLS to print that data

SELECT * 
FROM emp_mast_unpivot 
UNPIVOT INCLUDE NULLS 
( 
  sal FOR dept_no IN ( dept_no_10_sal, dept_no_20_sal, dept_no_30_sal) 
);

Result

EMP_IDEMP_CODEDEPT_NOSAL
1A1DEPT_NO_10_SAL1000
1A1DEPT_NO_20_SAL
1A1DEPT_NO_30_SAL
1A2DEPT_NO_10_SAL3000
1A2DEPT_NO_20_SAL
1A2DEPT_NO_30_SAL
1A3DEPT_NO_10_SAL
1A3DEPT_NO_20_SAL3000
1A3DEPT_NO_30_SAL
1A4DEPT_NO_10_SAL
1A4DEPT_NO_20_SAL
1A4DEPT_NO_30_SAL4000
1A5DEPT_NO_10_SAL2000
1A5DEPT_NO_20_SAL
1A5DEPT_NO_30_SAL

If you liked the above post, please leave your comments below.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

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