Introduction

In this article, we are going to learn how to Reverse and get the original table after applying the Pivot operation to the table in SQL.

Reversing Non-aggregate Pivoted Table

Reversing a PIVOT operator is only possible if the pivoted table doesn’t contain aggregated data.

Let’s look at the table we used in the Pivot Table In SQL article.

You can see that there are no repeated rows. In other words, we can say that for each student there is only one record per Subject.

When we applied the PIVOT operator to the above table we got the following result.

Now, we are going to apply the UNPIVOT operator to this result and see if we can get back to the original table. To do this, execute the following code:

SELECT
  [Student],
  [Subject],
  [Marks]
FROM (SELECT
  *
FROM (SELECT
  [Student],
  [Subject],
  [Marks]
FROM StudentGrades) AS StudentTable
PIVOT (
SUM([Marks])
FOR [Subject] IN ([Mathematics],
[Science],
[Geography])
) AS StudentPivot) PivotedResults
UNPIVOT
(
[Marks]
FOR [Subject] IN ([Mathematics],
[Science],
[Geography])
) AS Studentunpivot

Here we use a subquery to apply the UNPIVOT operator to the pivoted data. The inner query employs the PIVOT operator, while the outer query uses the UNPIVOT operator. At the output, you will see the original Students table.

• Output

As above output, you can see that it reverse your PIVOT table to your original table.

Reversing Aggregated Pivoted Table

As I said above that it is possible only to reverse a PIVOT operator that doesn’t contain aggregated data. Let us try to reverse the PIVOT operation that contains aggregated data.

For that insert one duplicate record in same table.

INSERT INTO StudentGrades VALUES ('John','Geography',90)

In above output you can see there is duplicate record of John.

Now we will apply pivot operation to above table and you can see the result as given below.

From the output, you can see that the SUM function in the PIVOT operator has added two scores to the Geography Subject taken by John. If you try to reverse the pivot of this table (i.e. apply the UNPIVOT operator), you will not receive the original table. It will return the six records instead of the original seven. The Geography column for the student John will contain the aggregated result rather than the individual results.

Now apply the above UNPIVOT operator code then you will get the following output.

Also, check Find Specific Text String In Stored Procedures, Functions, Views And Triggers In SQL Server