Post
Ask Question

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.
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
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.
  1. CREATE DATABASE School  
  2. GO  
  3. USE School  
  4. GO  
  5. CREATE TABLE Students  
  6. (  
  7.    Id INT PRIMARY KEY IDENTITY,  
  8.    StudentName VARCHAR (50),  
  9.    Course VARCHAR (50),  
  10.    Score INT  
  11. )  
  12. GO  
  13. INSERT INTO Students VALUES ('Sally', 'English', 95 )  
  14. INSERT INTO Students VALUES ('Sally', 'History', 82)  
  15. INSERT INTO Students VALUES ('Edward', 'English', 45)  
  16. 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,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
Let’s PIVOT this table by the Course column. To do this, execute the following script,
  1. SELECT * FROM  
  2. (SELECT StudentName, Score, Course FROM Students)  
  3. AS StudentTable  
  4. PIVOT(  
  5. SUM(Score)  
  6. FOR Course IN ([English],[History])  
  7. ) 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,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 

UNPIVOT Operator

 
The UNPIVOT operator is used to convert table columns into rows. For instance, if you have a table that looks like this,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
The UNPIVOT operator will return the following results,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
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,
  1. CREATE DATABASE school2   
  2. go   
  3. USE school2   
  4. go   
  5. CREATE TABLE students   
  6.   (   
  7.      id          INT PRIMARY KEY IDENTITY,   
  8.      studentname VARCHAR (50),   
  9.      math        INT,   
  10.      english     INT,   
  11.      history     INT,   
  12.      science     INT   
  13.   )   
  14. go   
  15. INSERT INTO students VALUES ('Sally', 78, 85, 91, 76 )   
  16. 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,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
To apply the UNPIVOT operator to this table, run the following query,
  1. SELECT studentname,   
  2.        course,   
  3.        score   
  4. FROM   students   
  5.        UNPIVOT ( score   
  6.                FOR course IN (math,   
  7.                               english,   
  8.                               history,   
  9.                               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,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 

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.
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
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,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
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.
  1. SELECT studentname,   
  2.        course,   
  3.        score   
  4. FROM   (SELECT *   
  5.         FROM   (SELECT studentname,   
  6.                        score,   
  7.                        course   
  8.                 FROM   students) AS StudentTable   
  9.                PIVOT( Sum(score)   
  10.                     FOR course IN ([English],   
  11.                                    [History]) ) AS schoolpivot) PivotedResults   
  12.        UNPIVOT ( score   
  13.                FOR course IN (english,   
  14.                               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,
  1. INSERT INTO Students VALUES ('Edward', 'History', 78)  
Now, if you select all the records from the Students table, you will get the following output,
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements
 
We can see that we have a duplicate record for Edward’s score in History.
 
Now, apply the PIVOT operator to this table.
  1. SELECT id,   
  2.        studentname,   
  3.        english,   
  4.        history   
  5. FROM   students   
  6.        PIVOT ( Sum (score)   
  7.              FOR course IN (english,   
  8.                             history) ) AS schoolpivot   
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements

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,
  1. SELECT studentname,   
  2.        course,   
  3.        score   
  4. FROM   (SELECT *   
  5.         FROM   (SELECT studentname,   
  6.                        score,   
  7.                        course   
  8.                 FROM   students) AS StudentTable   
  9.                PIVOT( Sum(score)   
  10.                     FOR course IN ([English],   
  11.                                    [History]) ) AS schoolpivot) PivotedResults   
  12.        UNPIVOT ( score   
  13.                FOR course IN (english,   
  14.                               history) ) AS schoolunpivot   
The output table will look like this.
 
Understanding PIVOT, UNPIVOT, And Reverse PIVOT Statements


Similar Articles