T-SQL - Pivoting And Unpivoting Data

When SELECTing information from the table(s), we mostly ask for grouped data. Grouping allows us to retrieve statistical data by some columns. Grouping is very important, but sometimes it is not just enough to “generate” final information.

Sometimes we need to rotate one of the columns to be as a row without changing the result of grouped data. Long story short, transforming data from a state of rows to a state of columns is called Pivoting.

PS: All examples use Adventurework2019.

Simple example without pivot: (classical grouping)

SELECT
  SalesOrderID
, SpecialOfferID
, SUM(UnitPrice) as TotalSum 
  FROM Sales.SalesOrderDetail AS SOD
  GROUP BY SalesOrderID, SpecialOfferID

In the above example, we get data grouped by SalesOrderId and SpecialOfferId.

Say your manager asked for the same data but he wants to see SpeciallOfferId in the columns. So from the above example, you will have columns like [1], and [2]. The main focus is SpecialOfferId and we need to somehow rotate the column to be not a column but a row.

T-SQL provides a special table operator to implement it: PIVOT.

Before writing PIVOT, let’s see what you need to know about the internal implementation of PIVOT. You should identify 3 elements:

1. What do you want to see on rows?
2. What do you want to see on columns?
3. What type of information should be rendered in the intersection of these columns and rows ( mostly it is your aggregate column)

Here is the syntax for implementing it,

 

As we learned from the article, Common Table Expression’s (CTE) use cases wider than Derived Tables and now we will implement PIVOT exactly with CTE.

WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT SalesOrderId, [1],[2] FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

As you can see, PIVOT is a special table expression and as a complex combination, it defines the aggregate column for the spread column.

Before switching to UNPIVOT, we need to understand some limitations of PIVOT:

1. We can use only one aggregate with PIVOT
2. The IN clause of the PIVOT operator accepts a static list of spreading values. You need to type them manually without any magic technique.
3. No way to use COUNT(*), use classical COUNT(<column name>)

Unpivot is a reverse operation for you PIVOT. If you have any table generated by u PIVOT, you can reverse this table to the “original one” with UNPIVOT.

Let’s use our query with some modifications to create a table that will store all PIVOTted data.

WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT * INTO MyPivottedTable FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

The above query will store all information inside new created MyPivottedTable table.

Now it is time for our magic UNPIVOT to ”restore” information to its original.

 SELECT SalesOrderID,SpecialOfferID, UnitPrice
  FROM MyPivottedTable
  UNPIVOT(UnitPrice FOR SpecialOfferId IN ([1],[2])) AS UNPVT

Here is the response: