PIVOT OPERATOR IN SQL SERVER

This article will explain PIVOT operator for beginners with a very simple example. We use PIVOT operator to transform data from row level to columnar level. It basically transform the table-valued expression by using the unique value from a column in the expression into multiple columns in the output and also performs aggregations wherever required on remaining column values, which we needed in output values.

Basically, Using PIVOT we can transform the unique values of a column into multiple columns. We will understand this with a very simple example.

Code for PIVOT

To understand PIVOT with a example, we will first create a temporary table #CourseSale_Details with some records as available in the below image by using the below mentioned script.

TEMP TABLE #CourseSale_Details

COURSE	Year	MONTH	EARNING
.NET	2012	MAY	10000
.NET	2012	MAY	125
.NET	2012	JUNE	NULL
Java	2012	MAY	0
Java	2012	JUNE	125
Java	2012	JUNE	20000

CREATE TABLE #CourseSale_Details

CREATE TABLE #CourseSale_Details(COURSE VARCHAR(50),Year INT, MONTH VARCHAR(15),EARNING  MONEY)

INSERT SOME RECORDS TO TABLE

INSERT INTO #CourseSale_Details VALUES('.NET',2012,'MAY',10000)

INSERT INTO #CourseSale_Details VALUES('.NET',2012,'MAY',125)

INSERT INTO #CourseSale_Details VALUES('.NET',2012,'JUNE',null)

INSERT INTO #CourseSale_Details VALUES('Java',2012,'MAY',0)

INSERT INTO #CourseSale_Details VALUES('Java',2012,'JUNE',125)

INSERT INTO #CourseSale_Details VALUES('Java',2012,'JUNE',20000)

In below mentioned example #CourseSale_Details table data is pivoted so that we can transform the desired unique column values from month column to multiple column.

EXAMPLE 1 (Select all Column values of a column):

In this example we are selecting all the unique values from MONTH column.

CODE FOR THE PIVOT

SELECT *

FROM #CourseSale_Details

PIVOT(SUM(Earning) FOR MONTH IN (MAY,JUNE)) AS PVTTable

OUTPUT:

COURSE	Year	MAY	JUNE
.NET	2012	10125	NULL
Java	2012	0	20125

EXAMPLE 2 (Select different Column values of a column):

In this example we are selecting only MAY out of MAY and JUNE  from MONTH column.

CODE FOR THE PIVOT

SELECT *

FROM #CourseSale_Details

PIVOT(SUM(Earning) FOR MONTH IN (MAY)) AS PVTTable

OUTPUT:

COURSE	Year	MAY
.NET	2012	10125
Java	2012	0

EXAMPLE 3 (Select additional column values which is not available in column):

In this example we are selecting additional value JULY which is not available in MONTH column.

CODE FOR THE PIVOT

SELECT *

FROM #CourseSale_Details

PIVOT(SUM(Earning) FOR MONTH IN (MAY,JUNE,JULY)) AS PVTTable

OUTPUT:

COURSE	Year	MAY	JUNE	JULY
.NET	2012	10125	NULL	NULL
Java	2012	0	20125	NULL

EXAMPLE 4 (want to see only specific pivoted columns):

In this example we are selecting additional value JULY which is not available in MONTH column to PIVOT but we are selecting only MAY Column to show in output.

CODE FOR THE PIVOT

SELECT COURSE,YEAR,MAY

FROM #CourseSale_Details

PIVOT(SUM(Earning) FOR MONTH IN (MAY,JUNE,JULY)) AS PVTTable

OUTPUT:

COURSE	YEAR	MAY
.NET	2012	10125
Java	2012	0