SQL Tutorials

What is Pivot in SQL with Example ?| Pivot Clause

Amit S March 26, 2017

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

CLICK HERE TO GET 20 MOST IMPORTANT SQL QUERIES FOR INTERVIEW

What is Pivot in SQL with examples?

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.

Syntax:

SELECT Column_name1,Column_name2…..FROM (SubQuery)
PIVOT [XML]
( pivot_clause_Aggregate_Function
pivot_for_clause
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.

4.Subquery:

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)

PIVOT

(Count(Employee_name)

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

Output:

100 101 103
2 1 1

Using With Clause:

   WITH Pivot_Department AS (

Select DepartmentId   from Department

)

SELECT *

FROM   Pivot_Department

PIVOT (

Count(Employee_name)        — pivot_clause

FOR DepartmentId                      — pivot_for_clause

IN  (100,101,102)                           — pivot_in_clause

);

Output:

100 101 103
2 1 1

Unpivot:

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.

Syntax:

SELECT Column_name1,Column_name2…

FROM

UNPIVOT [INCLUDE|EXCLUDENULLS]

( unpivot_clause

unpivot_for_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

as

Select * from

(Select DepartmentId,Employee_name from Department)

PIVOT

(Count(Employee_name)

For DepartmentId IN (100,101,102)

);

Select * from V_Pivot_Data;

Output:

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

Unpivot

(

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.

HOME