PIVOT and UNPIVOT in Sql Server

PIVOT and UNPIVOT in Sql Server

This article provides introduction to the PIVOT and UNPIVOT relational operators introduced in Sql Server 2005 with extensive list of examples.

ALSO READ: Dynamic PIVOT in Sql Server

PIVOT

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.

To understand PIVOT with extensive list of examples, let us first create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:

--Create Temporary Table #CourseSales 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

Example 1:

In this example the #CourseSales Table data is Pivoted so that the Course becomes the column headings.

Below script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.

SELECT * FROM #CourseSales PIVOT(SUM(Earning)       FOR Course IN ([.NET], Java)) AS PVTTable

Example 2:

In this example the #CourseSales Table data is Pivoted so that the Year becomes the column headings.

Below script pivots the #CourseSales Table data so that the Year columns distinct values are transformed as Columns in the result set as depicted in the above image.

SELECT * FROM #CourseSales PIVOT(SUM(Earning)       FOR Year IN ([2012],[2013])) AS PVTTable

Example 3:

Transforming Sales Table data to a Quarterly aggregated sales data with Quarters as the Columns in the result set.

Let us create a Temporary Sales table and insert 1000 Sample Sales Records With Random past 0-1000 days as the sales date by using the below script.

--Create Temporary Sales Table CREATE TABLE #Sales (SalesId INT IDENTITY(1,1), SalesDate DateTime) GO --Populate 1000 Sample Sales Records With --Random past 0-1000 days as sales date INSERT INTO #Sales(SalesDate) VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE())) GO 1000

Now we can write a query like below which Pivots the Sales Table Data such that the quarters are the columns in the final result set as depicted in the above image.

SELECT Year, QPivot.[1] As Q1, QPivot.[2] As Q2,  QPivot.[3] As Q3, QPivot.[4] As Q4 FROM (SELECT YEAR(SalesDate) [Year],      DATEPART(QUARTER, SalesDate) [Quarter],          COUNT(1) [Sales Count]       FROM #Sales       GROUP BY YEAR(SalesDate),          DATEPART(QUARTER,SalesDate)) AS QuarterlyData PIVOT( SUM([Sales Count])           FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

Example 4:

Transforming Sales Table data to a Monthly aggregated sales data with Months as the Columns in the result set.

We can write a PIVOT query like below to get the Monthly Data as depicted in the above image.

SELECT * FROM (SELECT YEAR(SalesDate) [Year],        DATENAME(MONTH, SalesDate) [Month],        COUNT(1) [Sales Count]       FROM #Sales       GROUP BY YEAR(SalesDate),       DATENAME(MONTH, SalesDate)) AS MontlySalesData PIVOT( SUM([Sales Count])       FOR Month IN ([January],[February],[March],[April],[May],     [June],[July],[August],[September],[October],[November],     [December])) AS MNamePivot

[ALSO READ] How to replace NULL value by 0 in the Pivot result

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

Examples of PIVOT and Dynamic PIVOT

Below are the some of the examples of retrieving data in Sql Server using PIVOT and Dynamic PIVOT:

• How to get Hourly data in Sql Server
• How to get Daily data in Sql Server
• How to get Monthly Data in Sql Server
• How to get Quarterly Data in Sql Server
• How to get Yearly data in Sql Server

ALSO READ: Dynamic PIVOT in Sql Server