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

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…

Insert rows into that table and commit…

Using PIVOT in SQL query

Result

TEN_TOTAL_SALARY TWENTY_TOTAL_SALARY
5000 7000

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

Result

EMP_CODE TEN_TOTAL_SALARY TWENTY_TOTAL_SALARY
A2 2000
A3 3000
A1 1000
A5 4000
A4 2000

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

Result

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

Result

EMP_ID DEPT_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…

Insert data into the table…

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.

Result

EMP_ID EMP_CODE DEPT_NO SAL
1 A1 DEPT_NO_10_SAL 1000
2 A2 DEPT_NO_10_SAL 3000
3 A3 DEPT_NO_20_SAL 3000
4 A4 DEPT_NO_30_SAL 4000
5 A5 DEPT_NO_10_SAL 2000

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

Result

EMP_ID EMP_CODE DEPT_NO SAL
1 A1 DEPT_NO_10_SAL 1000
1 A1 DEPT_NO_20_SAL
1 A1 DEPT_NO_30_SAL
1 A2 DEPT_NO_10_SAL 3000
1 A2 DEPT_NO_20_SAL
1 A2 DEPT_NO_30_SAL
1 A3 DEPT_NO_10_SAL
1 A3 DEPT_NO_20_SAL 3000
1 A3 DEPT_NO_30_SAL
1 A4 DEPT_NO_10_SAL
1 A4 DEPT_NO_20_SAL
1 A4 DEPT_NO_30_SAL 4000
1 A5 DEPT_NO_10_SAL 2000
1 A5 DEPT_NO_20_SAL
1 A5 DEPT_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.