PIVOT and UNPIVOT in Sql Server

In this post, we want to discuss four examples of PIVOT and UNPIVOT in SQL Server. Before we get started, if you face a large size SQL Log file and want to reduce it as soon as possible, please go through the following article: Reduce log file size to just 1mb.

Introduction PIVOT and UNPIVOT

PIVOT and UNPIVOT are one of the new relational operators introduced in SQL Server 2005 to change a table-valued expression into another table. PIVOT provides an easy mechanism in SQL Server to rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

To understand PIVOT with an 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: 

Example 1

In this example, the #CourseSales Table data is Pivoted so that the Course becomes the column headings. The below script pivots the #CourseSales Table data so that the Course column’s distinct values are transformed as Columns in the result set as depicted in the above image.

Example 2

In this example, the #CourseSales Table data is Pivoted so that the Year becomes the column headings. The below script pivots the #CourseSales Table data so that the Year column’s distinct values are transformed as Columns in the result set as depicted in the above image.

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 date of the sales by using the below script.

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.

Example 4

Transforming Sales Table data to 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.

UNPIVOT

UNPIVOT is the reversal of the PIVOT operation. It 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 before the PIVOT operation.

As depicted in the above image there were 5 rows in the #CourseSales Table Before PIVOT, but a PIVOT and its reversal UNPIVOT resulted in 4 rows only. The reason for this is for .NET Course in the Year 2012 there were two records one with earning 10K and another with earning 5K, the PIVOT and its reversal UNPIVOT result last lost this split information, and instead of two rows, it has generated one row for the .NET course in the Year 2012 with Earning as the 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 and UNPIVOT

The article was published on March 21, 2017 @ 9:32 AM