PIVOT and UNPIVOT

PIVOT is one of the New relational operator introduced in Sql Server 2005. It provides an easy mechanism in Sql Server to transform rows into columns.

Example:

CREATE TABLE #CourseSales

(Course VARCHAR(50),Year INT,Earning  MONEY)

GO

--Populate Sample records

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

INSERT INTO #CourseSales VALUES('Java',2012,20000)

INSERT INTO #CourseSales VALUES('.NET',2012,5000)

INSERT INTO #CourseSales VALUES('.NET',2013,48000)

INSERT INTO #CourseSales VALUES('Java',2013,30000)

GO

select * from #CourseSales

 

By Using PIVOT Operator

select * from #CourseSales

pivot(sum(earning) for course in([.net],[Java])) as PIVOTTABLE

SELECT *

FROM #CourseSales

PIVOT(SUM(Earning) 

      FOR Year IN ([2012],[2013])) AS PVTTable

By Using UNPIVOT

UNPIVOT is the reversal of the PIVOT operation. It basically provides a mechanism for transforming columns into rows.

From the above image it is clear that UNPIVOT is the reversal of the PIVOT operation. But it is not the exact reversal of PIVOT operation as PIVOT operation generates the aggregated result, so UNPIVOT will not be able to split the aggregated result back to the original rows as they were present prior to the PIVOT operation.

As depicted in the above image there were 5 rows in the #CourseSales Table Prior to PIVOT, but a PIVOT and it’s reversal UNPIVOT resulted in 4 rows only. The reason for this is for .NET Course in Year 2012 there were two records one with earning 10K and another with earning 5K, the PIVOT and it’s reversal UNPIVOT result last lost this split information and instead of two rows it has generated one row for the .NET course in Year 2012 with Earning as sum of 10K and 5K i.e. 15K.

We can use the below script to simulate the PIVOT and UNPIVOT operation as depicted in the above image on the #CourseSales Temporary Table created in the beginning PIVOT section of this article.

--PIVOT the #CourseSales table data on the Course column

SELECT *

INTO #CourseSalesPivotResult

FROM #CourseSales

PIVOT(SUM(Earning)

      FOR Course IN ([.NET], Java)) AS PVTTable

GO

--UNPIVOT the #CourseSalesPivotResult table data

--on the Course column   

SELECT Course, Year, Earning

FROM #CourseSalesPivotResult

UNPIVOT(Earning

      FOR Course IN ([.NET], Java)) AS UNPVTTable