Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.
reverse pivot table sql

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
convert rows to columns sql

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.
pivot and unpivot table sql server

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
pivot and unpivot examples in sql server

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.
undo pivot table sql server

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.