Reverse PIVOT Table in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

How to Reverse PIVOT Table in SQL Server

In this article, I am going to discuss How to Reverse the PIVOT Table in SQL Server. Please read our previous article before proceeding to this article where we discussed How to Implement PIVOT and UNPIVOT in SQL Server in detail. Here, first, we will discuss whether it is always possible to reverse what the PIVOT operator has done using the UNPIVOT operator and how we can reverse a PIVOT table in SQL Server.

Is it always possible to reverse what the PIVOT operator has done using UNPIVOT operator in SQL Server?

The answer is No. Always it is not possible to reverse what the PIVOT operator does using the UNPIVOT operator. This basically depends on the aggregation of data. There might be two scenarios, they are as follows:

1. If the PIVOT operator aggregated the data, then you will not get the original data back using the UNPIVOT operator.
2. If the PVOT operator has not aggregated the data, then you will get the original data back using the UNPIVOT operator.

If this is not clear at the moment, then don’t worry we will try to understand this with some examples.

Example:

We are going to use the following ProductSales table to understand this concept. Please have a look at the moment we don’t have any SalesAgent with two records for the same country.

Please use the below SQL Script to create and populate ProductSales the table with the required data.

Create Table ProductSales
(
     SalesAgenName VARCHAR(50),
     SalesCountryName VARCHAR(20),
     SalesAmount INT
)
Go

INSERT INTO ProductSales VALUES ('James', 'India', 9260)
INSERT INTO ProductSales VALUES ('James', 'US',5280)
INSERT INTO ProductSales VALUES ('Pam', 'India',9770)
INSERT INTO ProductSales VALUES ('Pam', 'US',2540)
INSERT INTO ProductSales VALUES ('David', 'India',9970)
INSERT INTO ProductSales VALUES ('David', 'US',5405)
Go

Convert Row-wise data into column-wise using the PIVOT operator

The following SQL Query will convert the row-wise data into column-wise.

SELECT SalesAgenName, India, US
FROM
(
   SELECT SalesAgenName, SalesCountryName,  SalesAmount 
   FROM ProductSales
) AS PivotData
PIVOT
(
 Sum (SalesAmount) FOR SalesCountryName 
 IN (India, US)
) AS PivotTable

Once you execute the above query, you should get the following output.

How to use the UNPIVOT operator to reverse what the PIVOT operator has done?

To understand this, please have a look at the following image. We already discussed how to use the PIVOT and UNPIVOT operators in our previous article. As you can see in the below image, we use the UNPIVOT operator on the result set which is return by the PIVOT operator to reverse the data.

Let us execute the following script and see the output.

SELECT SalesAgenName, SalesCountryName, SalesAmount
FROM

 -- PIVOT Section
 (
 SELECT SalesAgenName, India, US
 FROM
 (
  SELECT SalesAgenName, SalesCountryName,  SalesAmount 
  FROM ProductSales
 ) AS PivotData
 PIVOT
 (
  Sum(SalesAmount) FOR SalesCountryName 
  IN (India, US)
 ) AS PivotTable) PTable

UNPIVOT
(
     SalesAmount
     FOR SalesCountryName IN (India, US)
) AS UnpivotTable

Once you execute the above query, you will get the following output and you can compare this output with the original ProductSales table data. Here, we get the original data back. This is because the SUM aggregate function that we used with the PIVOT operator does not aggregate the data as there is no SalesAgent with multiple records for the same country. So, the output that we get is not aggregated.

An agent with Multiple records for the same country:

Please execute the following INSERT statement to insert a new record into the ProductSales table.

INSERT INTO ProductSales VALUES (‘James’, ‘India’, 1200)

Once you execute the above INSERT statement, now the SalesAgent James has two records in the ProductSales table. Now execute the following SQL Script and see the output.

SELECT SalesAgenName, India, US
FROM
(
   SELECT SalesAgenName, SalesCountryName,  SalesAmount 
   FROM ProductSales
) AS PivotData
PIVOT
(
 Sum (SalesAmount) FOR SalesCountryName 
 IN (India, US)
) AS PivotTable

Once you execute the above query, you will get the following output. We have two records for Agent James and for India country. Please have a look at the second row (James) and India column value. Here, you can see, it sums the SalesAmount value for India country and displays it on the India column. So, the data is now aggregated.

Now if we use the UNPIVOT operator with the above query, then we wouldn’t get the original data back. This is because the PIVOT operator already aggregated the data, and there is no way for SQL Server to know how to undo the aggregations.

Please execute the below SQL query.

SELECT SalesAgenName, SalesCountryName, SalesAmount
FROM

 -- PIVOT Section
 (
 SELECT SalesAgenName, India, US
 FROM
 (
  SELECT  SalesAgenName, SalesCountryName,  SalesAmount 
  FROM ProductSales
 ) AS PivotData
 PIVOT
 (
  Sum(SalesAmount) FOR SalesCountryName 
  IN (India, US)
 ) AS PivotTable ) PTable

UNPIVOT
(
     SalesAmount
     FOR SalesCountryName IN (India, US)
) AS UnpivotTable

Once you execute the above query, you will get the following output. As you can see, for the SalesAgent James and Country India, we get only one row. But in the original ProductSales table, we had 2 rows for the same combination.

So, this proves that it is not always possible to reverse what the PIVOT operator has done using the UNPIVOT operator and get the original data back.

In the next article, I am going to discuss How to Improve Performance in SQL Server. Here, in this article, I try to explain How to reverse PIVOT Table in SQL Server with Examples. I hope you enjoy this How to reverse PIVOT Table in SQL Server with Examples article.