What is Pivot in SQL with Example ?| Pivot Clause

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.”

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

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:

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:

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:

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:

1. Check  out your SQL topics :
   • History of SQL
   • SQL Create Table(DDL in SQL)
   • SQL DML Statements(INSERT,UPDATE,DELETE)
   • SQL Select Statement Execution
   • Operators in SQL
   • Views in SQL
   • Materialized View in SQL
   • Joins in SQL
   • Inner Join / Outer Join
   • Full Outer Join / Cartesian Join
   • Union and Union ALL
   • Intersect and Minus
   • Indexing in SQL
   • Rank and Dense Rank
   • SubQueries and Correlated Subqueries
   • Parser and Optimizer
   • Oracle 11 G new Features
   • SQL Functions List
   • Constraints in SQL
   • Database Normalization
   • Table Partitioning
   • Pivot in SQL
   • Difference Between Truncate,Delete and drop
   • Oracle System Tables
   • Oracle 18c
   • Oracle 18c impact on DBA
   • Oracle 18c Advantages
   • Oracle 18c Disadvantages

   Unix Tutorials :

   • What is unix?
   • Basic unix commands
   • File Commands in unix
   • Create File in Unix using multiple ways
   • Cat Command
   • Touch Command
   • Mkdir command
   • rmdir Command
   • pwd command
   • Cd Command
   • cut Command
   • paste Command
   • tr Command
   • Cp Command
   • wc command
   • cmp command
   • Rm Command
   • Grep Command
   • Egrep Command
   • FGrep Command
   • Vi Editor 
   • Unix File Permissions
   • Chmod command

   Oracle Business Intelligence Tutorial :

   • What is BI?
   • What is Data warehouse?
   • Data modeling in BI
   • OLAP vs OLTP
   • Star schema
   • Snowflakes Schema
   • Advantages of OBIEE
   • Features of OBIEE
   • OBIEE Architecture
   • OBIEE Components
   • Working with OBIEE Repository(RPD)
   • Physical Layer of RPD
   • Business Model Layer of RPD
   • Errors in RPD
   • Consistency Check in RPD

   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