Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example

Pivot in SQL:

In previous article i have explained about Oracle 11 G features;One of the function named Pivot is new Oracle 11 G feature which is used specifically to transpose or convert rows in to columns or columns in to rows (Unpivot) to display the result in crosstab format. The simple meaning of Pivot in English is ‘Center point on  which mechanism turns or oscillates’.Just like that Pivot in SQL is used to convert the column values in to attributes(transpose rows in to columns).

“Pivot in SQL helps to convert column values into attributes or transpose rows into columns.”

Pivot in SQL


Following are the simple steps to perform the Pivoting:

1. Seperate the Rows:

The first step is seperate all rows.

2.Aggregate Required Data:

We need to aggregate the required data using the aggregate functions like Sum,Avg,Min,Max,Count function.

3.Convert aggregated data into columns:

The last step is to transpose aggregated data into column.


SELECT Column_name1,Column_name2…..FROM (SubQuery)
( pivot_clause_Aggregate_Function
pivot_in_clause )
WHERE  Condition;

Following are different parameters and arguments used in Pivot/Unpivot:

1.Pivot Clause with Aggregate Function:

There must be the aggregate function to Pivot the table. The aggregate functions like Sum,Avg,Min,Max and Count needs to be used for Pivoting table.

2.Pivot For Clause:

The Column name which needs to be converted from rows to column.

3.Pivot IN Clause:

These are nothing but the list of values to column 2 to pivot it in to headings in to cross table result.


We need to use the Subquery for fetching the records instead of list of values.In this case result of subquery would be used to determine the values from column to pivot in to headings.

Real Life Example:

Consider Following table.We need count of Employees department wise where Department ID is column.

Name of Table: Department

Department ID Employee Name
100 Amit
100 Rohan
101 Rohit
102 Pradnya

We need to convert the Department ID column in to Rows and then We need to display the count of  employees .

Select * from

(Select DepartmentId from Department)



For DepartmentId IN (100,101,102)


Using above Pivot statement the DepartmentId is pivoted and the table is been transposed and we are using Count() as aggregate function


100 101 103
2 1 1

Using With Clause:

   WITH Pivot_Department AS (

Select DepartmentId   from Department



FROM   Pivot_Department


Count(Employee_name)        — pivot_clause

FOR DepartmentId                      — pivot_for_clause

IN  (100,101,102)                           — pivot_in_clause



100 101 103
2 1 1


We have checked the Pivot in SQL which is used to convert the rows in to columns.Unpivot simply means opposite of pivot which is used in opposite of Pivot table but without dis-aggregating the data.One row of data for every column is unpivoted.

The Unpivot operator converts column based  data in to individual rows.


SELECT Column_name1,Column_name2…



( unpivot_clause


unpivot_in_clause )

WHERE  Condition;

The Syntax of Unpivot is quite similar to Pivot but there are some differences:

  • unpivot_clause: this clause specifies a name for a column to represent the unpivoted measure values. In our previous pivot examples, the measure column was the Count employee department grouping.
  • unpivot_for_clause: the unpivot_for_clause specifies the name for the column that will result from our unpivot query. The data in this column describes the measure values in the unpivot_clause column; and
  • unpivot_in_clause: this contains the list of pivoted columns (not values) to be unpivoted.

We need to convert new object for Pivoted data.So Consider to convert the view for the pivoted data.

Create view V_Pivot_Data


Select * from

(Select DepartmentId,Employee_name from Department)



For DepartmentId IN (100,101,102)


Select * from V_Pivot_Data;


Employee Name 100 101 102
Amit 1 0 0
Rohan 1 0 0
Rohit 0 1 0
Pradnya 0 0 1

We will now unpivot the data:

select * from V_Pivot_Data



Employee_name,             –unpivot call

For DepartmentId IN (100,101,102)


The output of the Query will be:

Department ID Employee Name
100 Amit
100 Rohan
101 Rohit
102 Pradnya
  1. Check  out your SQL topics :

    Unix Tutorials :

    Oracle Business Intelligence Tutorial :

    Click Here for SQL interview Questions

Hope you will get idea of pivot and unpivot operators in SQL.These two operators are very important operators used in Day to day life.Hope you like this article.



About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

5 Trackbacks / Pingbacks

  1. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  2. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  3. Basic Unix Commands | Frequently Used Unix Commands | Unix Commands with Examples | Useful Unix Commands
  4. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  5. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command

Leave a Reply

Your email address will not be published.