Pages - Menu

Wednesday, May 13, 2015

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

No comments:

Post a Comment