Reverse PIVOT table in SQL Server

Suggested Videos
Part 117 - Difference between rows and range
Part 118 - LAST_VALUE function in SQL Server
Part 119 - UNPIVOT in SQL Server



In this video we will discuss if it's always possible to reverse what PIVOT operator has done using UNPIVOT operator.



Is it always possible to reverse what PIVOT operator has done using UNPIVOT operator.
No, not always. If the PIVOT operator has not aggregated the data, you can get your original data back using the UNPIVOT operator but not if the data is aggregated.

Let us understand this with an example. We will use the following table tblProductSales for the examples in this video.


SQL Script to create tblProductSales table 

Create Table tblProductSales

(

     SalesAgent nvarchar(10),

     Country nvarchar(10),

     SalesAmount int

)

Go

Insert into tblProductSales values('David','India',960)

Insert into tblProductSales values('David','US',520)

Insert into tblProductSales values('John','India',970)

Insert into tblProductSales values('John','US',540)

Go

Let's now use the PIVOT operator to turn ROWS into COLUMNS

SELECT SalesAgent, India, US

FROM tblProductSales

PIVOT

(

     SUM(SalesAmount)

     FOR Country IN (India, US)

) AS PivotTable

The above query produces the following output


Now let's use the UNPIVOT operator to reverse what PIVOT operator has done

SELECT SalesAgent, Country, SalesAmount

FROM

(SELECT SalesAgent, India, US

FROM tblProductSales

PIVOT

(

     SUM(SalesAmount)

     FOR Country IN (India, US)

) AS PivotTable) P

UNPIVOT

(

     SalesAmount

     FOR Country IN (India, US)

) AS UnpivotTable

The above query reverses what PIVOT operator has done, and we get the original data back as shown below. We are able to get the original data back, because the SUM aggregate function that we used with the PIVOT operator did not perform any aggregation.


Now execute the following INSERT statement to insert a new row into tblProductSales table.

Insert into tblProductSales values('David','India',100)

With this new row in the table, if you execute the following PIVOT query data will be aggregated

SELECT SalesAgent, India, US

FROM tblProductSales

PIVOT

(

     SUM(SalesAmount)

     FOR Country IN (India, US)

) AS PivotTable

The following is the result of the above query


Now if we use UNPIVOT opertaor with the above query, we wouldn't get our orginial data back as the PIVOT operator has already aggrgated the data, and there is no way for SQL Server to know how to undo the aggregations.

SELECT SalesAgent, Country, SalesAmount

FROM

(SELECT SalesAgent, India, US

FROM tblProductSales

PIVOT

(

     SUM(SalesAmount)

     FOR Country IN (India, US)

) AS PivotTable) P

UNPIVOT

(

     SalesAmount

     FOR Country IN (India, US)

) AS UnpivotTable

Notice that for SalesAgent - David and Country - India we get only one row. In the original table we had 2 rows for the same combination.