Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements

The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.

In this article, we will study these three concepts with different examples.

PIVOT Operator

As mentioned earlier, the PIVOT operator converts table rows into columns. For example, if you have a table that looks like this.

Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements

and pivot it by the third column, the result will be as follows.

In the original table, we had two unique values for the Course columns – English and History. In the pivoted table, these unique values have been converted into columns. You can see that the score values for each new column remain unchanged. For instance, in the original table, a student, Sally, had scored 95 in English, unlike the values in the pivoted table.

As ever ensure that you make a secure backup before making any adjustments to a live database.

Let’s look at this example of using the PIVOT operator in SQL Server.

CREATE DATABASE School
GO
USE School
GO
CREATE TABLE Students
(
Id INT PRIMARY KEY IDENTITY,
StudentName VARCHAR (50),
Course VARCHAR (50),
Score INT
)
GO
INSERT INTO Students VALUES ('Sally', 'English', 95 )
INSERT INTO Students VALUES ('Sally', 'History', 82)
INSERT INTO Students VALUES ('Edward', 'English', 45)
INSERT INTO Students VALUES ('Edward', 'History', 78)

The script above creates the School database. In the database, we create the Students table with four columns, such as Id, StudentName, Course, and Score. Finally, we add the four dummy records to the Students table.

Now, if you use a SELECT statement to retrieve all the records, you will see the following,

Let’s PIVOT this table by the Course column. To do this, execute the following script,

SELECT * FROM
(SELECT StudentName, Score, Course FROM Students)
AS StudentTable
PIVOT(
SUM(Score)
FOR Course IN ([English],[History])
) AS SchoolPivot

Let’s see what is happening in the script. In the first line, we use the SELECT statement to define the columns that we want to add to the pivoted table. The first two columns are StudentName and Score. The data for these two columns will come directly from the Students table. The third column is Course. We want to PIVOT our table by the Course column, therefore, the Course column will be split into the number of columns equal to the values specified by the PIVOT operator for the Course column.

The syntax for the PIVOT operator is simple. First, you have to apply an aggregate function to the column the values of which you want to display in the pivoted columns. In our case, we want to show Score in the pivoted columns – English and History. Finally, we use a FOR statement to specify the pivot column and the unique values in it. The result looks like this,

UNPIVOT Operator

The UNPIVOT operator is used to convert table columns into rows. For instance, if you have a table that looks like this,

The UNPIVOT operator will return the following results,

The columns of the original table have been converted to the rows in the unpivoted table. Let’s use that data to see how the UNPIVOT operator works in SQL.

To do this, execute the following script,

CREATE DATABASE school2
go
USE school2
go
CREATE TABLE students
(
id INT PRIMARY KEY IDENTITY,
studentname VARCHAR (50),
math INT,
english INT,
history INT,
science INT
)
go
INSERT INTO students VALUES ('Sally', 78, 85, 91, 76 )
INSERT INTO students VALUES ('Edward', 87, 90, 82, 87)

If you select the data from the Students table of the School2 database, you will see the following results,

To apply the UNPIVOT operator to this table, run the following query,

SELECT studentname,
course,
score
FROM students
UNPIVOT ( score
FOR course IN (math,
english,
history,
science) ) AS schoolunpivot

The syntax for the UNPIVOT operator is similar to the PIVOT one. In the SELECT statement, you need to specify the columns you want to add to the output table. In the UNPIVOT statement, you will specify two columns:

The first column contains the values from the rows of the pivoted columns (which is Score in this case).
The second column includes the names of the pivoted columns, i.e. Math, English, History, and Science.

The output table will look like this,

Reversing a PIVOT

Reversing a PIVOT operator refers to the process of applying the UNPIVOT operator to a pivoted table in order to get back to the original table.

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 section of this 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 course. For example, Sally has only one record for her score in the English course.

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 script,

Note

We can execute this query on the School database that we created in the PIVOT operator section.

SELECT studentname,
course,
score
FROM (SELECT *
FROM (SELECT studentname,
score,
course
FROM students) AS StudentTable
PIVOT( Sum(score)
FOR course IN ([English],
[History]) ) AS schoolpivot) PivotedResults
UNPIVOT ( score
FOR course IN (english,
history) ) AS schoolunpivot

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.

Reversing Aggregated Pivoted Table

We said earlier that it is possible only to reverse a PIVOT operator that doesn’t contain aggregated data. Let’s try to reverse the PIVOT statement that contains aggregated data.

Add another record to the Students table of the School database that we created in the first section of this article. To do this, run the following query,

INSERT INTO Students VALUES ('Edward', 'History', 78)

Now, if you select all the records from the Students table, you will get the following output,

We can see that we have a duplicate record for Edward’s score in History.

Now, apply the PIVOT operator to this table.

SELECT id,
studentname,
english,
history
FROM students
PIVOT ( Sum (score)
FOR course IN (english,
history) ) AS schoolpivot

From the output, you can see that the SUM function in the PIVOT operator has added two scores to the History course taken by Edward. 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 four records instead of the original five. The History column for the student Edward will contain the aggregated result rather than the individual results.

To see this, execute the following script,

SELECT studentname,
course,
score
FROM (SELECT *
FROM (SELECT studentname,
score,
course
FROM students) AS StudentTable
PIVOT( Sum(score)
FOR course IN ([English],
[History]) ) AS schoolpivot) PivotedResults
UNPIVOT ( score
FOR course IN (english,
history) ) AS schoolunpivot

The output table will look like this.