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 to retrieve the original dataset.

In this article, we will study these three concepts of 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:

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 the 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:

1. The first column contains the values from the rows of the pivoted columns (which is Score in this case).
2. 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 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:

If you found this note useful, continue reading on the blog — https://codingsight.com/understanding-pivot-unpivot-and-reverse-pivot-statements/

Thank you and stay tuned!