Pivot And Unpivot In SQL Server

In this article we will learn how to use the pivot and unpivot operators in SQL Server.

SQL PIVOT and UNPIVOT are two relational operators that are used to convert a table expression into another. PIVOT is used when we want to transfer data from row level to column level and UNPIVOT is used when we want to convert data from column level to row level. PIVOT and UNPIVOT relational operators are used to generate a multidimensional reporting.

In this article, we will discuss both Pivot and Unpivot operators in a SQL Server database and how to create interactive tables that quickly combines and compares large amount of data.

Firstly, we will create a table and insert some data into the table. You may use your own database tables.

Here is the Employee table query.

  1. CREATE TABLE Employee  
  2. (  
  3.    Name [nvarchar](max),  
  4.    [Year] [int] ,  
  5.    Sales [int]  
  6. )  
Insert the following data into the table.
  1. INSERT INTO Employee  
  2. SELECT 'Pankaj',2010,72500 UNION ALL  
  3. SELECT 'Rahul',2010,60500 UNION ALL  
  4. SELECT 'Sandeep',2010,52000 UNION ALL  
  5. SELECT 'Pankaj',2011,45000 UNION ALL  
  6. SELECT 'Sandeep',2011,82500 UNION ALL  
  7. SELECT 'Rahul',2011,35600 UNION ALL  
  8. SELECT 'Pankaj',2012,32500 UNION ALL  
  9. SELECT 'Pankaj',2010,20500 UNION ALL  
  10. SELECT 'Rahul',2011,200500 UNION ALL  
  11. SELECT 'Sandeep',2010,32000   
Now we check data of Employee table.
  1. SELECT * FROM Employee;  
Output

Pivot And Unpivot In SQL Server

We use above Employee table for PIVOT and UNPIVOT relational operator examples.

PIVOT in SQL Server


PIVOT relational operator converts data from row level to column level. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. Using PIVOT operator, we can perform aggregate operation where we need them.

Syntax
  1. SELECT <non-pivoted column>,  
  2.        <list of pivoted column>  
  3. FROM  
  4. (<SELECT query  to produces the data>)  
  5.     AS <alias name>  
  6. PIVOT  
  7. (  
  8. <aggregation function>(<column name>)  
  9. FOR  
  10. [<column name that  become column headers>]  
  11.     IN ( [list of  pivoted columns])  
  12.   
  13. AS <alias name  for  pivot table>  
Let us take some examples.

Example 1
  1. SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  2. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  3. PIVOT  
  4. (  
  5. SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  6. ORDER BY [Tab2].[Year]  
Output

Pivot And Unpivot In SQL Server

In above query, we calculated the sum of sales for Pankaj, Rahul and Sandeep employees corresponding to the year values.

Example 2
 
Here is another example:
  1. SELECT Name, 2010,2011,2012 FROM   
  2. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  3. PIVOT  
  4. (  
  5. SUM(Sales) FOR [YearIN (2010,2011,2012)) AS Tab2  
  6. ORDER BY Tab2.Name  
Output

Pivot And Unpivot In SQL Server

When we execute the above query, SQL Server throws an error because we can’t provide integer value as a column name directly. To remove this error use the brackets before each integer value as in the following code snippet:
  1. SELECT Name, [2010],[2011],[2012] FROM   
  2. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  3. PIVOT  
  4. (  
  5. SUM(Sales) FOR [YearIN ([2010],[2011],[2012])) AS Tab2  
  6. ORDER BY Tab2.Name  
Output

Pivot And Unpivot In SQL Server

Example 3

In the previous examples, we wrote the name of pivot columns. This approach is useful if we know all possible values for pivot columns. But what if the number of the columns change in the database?

Let us take previous example. In previous example, we used 2010, 2011 and 2012 as pivot columns. But it is not fixed that these column will not change in future. What happens when we get data from year 2013? 2014? or even more?

To solve this problem, we need to use dynamic queries.
 
First, we retrieve all unique values from a pivot column and after that we will write a dynamic query to execute it with pivot query at run time.

Now we can execute example 2, but using the dynamic query.
  1. /*Declare Variable*/  
  2. DECLARE @Pivot_Column [nvarchar](max);  
  3. DECLARE @Query [nvarchar](max);  
  4.   
  5. /*Select Pivot Column*/  
  6. SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(YearFROM  
  7. (SELECT DISTINCT [YearFROM Employee)Tab  
  8.   
  9. /*Create Dynamic Query*/  
  10. SELECT @Query='SELECT Name, '+@Pivot_Column+'FROM   
  11. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  12. PIVOT  
  13. (  
  14. SUM(Sales) FOR [YearIN ('+@Pivot_Column+')) AS Tab2  
  15. ORDER BY Tab2.Name'  
  16.   
  17. /*Execute Query*/  
  18. EXEC  sp_executesql  @Query  
Output

Pivot And Unpivot In SQL Server

UNPIVOT in SQL Server


UNPIVOT relational operator is reverse process of PIVOT relational operator. UNPIVOT relational operator convert data from column level to row level.

Example 4

Suppose that output of example 2 is stored in a Temp variable. Now we want to rotate column identifiers Pankaj, Sandeep, Rahul into row values. For this, we use the UNPIVOT relational operator.

Declare Temp Variable
  1. DECLARE @Tab TABLE  
  2. (  
  3.    [Yearint,  
  4.    Pankaj int,  
  5.    Rahul int,  
  6.    Sandeep int  
  7. )  
Insert Value in Temp Variable
  1. INSERT INTO @Tab  
  2. SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  3. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  4. PIVOT  
  5. (  
  6.    SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  7.    ORDER BY [Tab2].[Year]  
Perform UNPIVOT Operation
  1. SELECT Name,[Year] , Sales FROM @Tab t  
  2. UNPIVOT  
  3. (  
  4. Sales FOR Name IN (Pankaj,Rahul,Sandeep)  
  5. AS TAb2  
Output

Pivot And Unpivot In SQL Server

We can perform first PIVOT operation and after that UNPIVOT operation on same table in single query as in the following code snippet.
  1. SELECT Name,[Year] , Sales FROM   
  2. (  
  3.    SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  4.    (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  5. PIVOT  
  6. (  
  7.    SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  8. )Tab  
  9. UNPIVOT  
  10. (  
  11.    Sales FOR Name IN (Pankaj,Rahul,Sandeep)  
  12. AS TAb2  
Output

Pivot And Unpivot In SQL Server

Note

UNPIVOT operation is a reverse process of PIVOT operation, but UNPIVOT is not the exact reverse of PIVOT. If PIVOT performs an aggregation and merges multiple rows into a single row in the output, then UNPIVOT can’t reproduce the original table-valued expression result because rows have been merged. So conclusion is that if PIVOT operation merges multiple row in a single row, then UNPIVOT operation can’t retrieve original table from the output of PIVOT operation. But if PIVOT operation doesn’t merge multiple row in a single row, then UNPIVOT operation can retrieve original table from the output of PIVOT operation.

Let us take an example,

Case 1. PIVOT merges multiple rows

Pivot And Unpivot In SQL Server
 
Now we perform PIVOT and UNPIVOT operation for this table and compare the resultant table from this table.
  1. SELECT Name,[Year] , Sales FROM   
  2. (  
  3.    SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  4.    (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  5. PIVOT  
  6. (  
  7.    SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  8. )Tab  
  9. UNPIVOT  
  10. (  
  11.    Sales FOR Name IN (Pankaj,Rahul,Sandeep)  
  12. AS TAb2  
Output

Pivot And Unpivot In SQL Server

We can see that both the tables are not same. First table contains 10 rows but the above table contains 7 rows only. This difference occurs due to the PIVOT operation. PIVOT operation merges (4,7,8) rows in a single row and merges (3,5) row in a single row. That's why the UNPIVOT operation can’t retrieve original table.

Case 2. PIVOT doesn’t merge multiple rows

Pivot And Unpivot In SQL Server
 
Now let's perform PIVOT and UNPIVOT operation for this table and compare the result of the new table with this table.
  1. SELECT Name,[Year] , Sales FROM   
  2. (  
  3.    SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  4.    (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  5. PIVOT  
  6. (  
  7.    SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  8. )Tab  
  9. UNPIVOT  
  10. (  
  11.    Sales FOR Name IN (Pankaj,Rahul,Sandeep)  
  12. AS TAb2  
Output

Pivot And Unpivot In SQL Server

We can see that both the tables are same.

Conclusion


Today, we learned about SQL PIVOT and UNPIVOT relational operators and learned how to convert a table expression into another. We should remember that UNPIVOT operation is the reverse process of PIVOT operation, but UNPIVOT is not the exact reverse of PIVOT.