Pivot And Unpivot In SQL Server

Article

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. Today we will discuss both the operators. PIVOT and UNPIVOT relational operators are used to generate an interactive table that quickly combines and compares large amount of data.

Firstly, we will create a table and insert some data into the table.

Firstly, create an Employee Table

CREATE TABLE Employee
(
Name [nvarchar](max),
[Year] [int] ,
Sales [int]
)

Insert the following data into the table

INSERT INTO Employee
SELECT 'Pankaj',2010,72500 UNION ALL
SELECT 'Rahul',2010,60500 UNION ALL
SELECT 'Sandeep',2010,52000 UNION ALL
SELECT 'Pankaj',2011,45000 UNION ALL
SELECT 'Sandeep',2011,82500 UNION ALL
SELECT 'Rahul',2011,35600 UNION ALL
SELECT 'Pankaj',2012,32500 UNION ALL
SELECT 'Pankaj',2010,20500 UNION ALL
SELECT 'Rahul',2011,200500 UNION ALL
SELECT 'Sandeep',2010,32000

Now we check data of Employee table.

SELECT * FROM Employee;

Output

We use above Employee table for PIVOT and UNPIVOT relational operator examples. First we read about PIVOT realational operator.

PIVOT

PIVOT relational operator convert 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 required.

Syntax

SELECT <non-pivoted column>,
<list of pivoted column>
FROM
(<SELECT query to produces the data>)
AS <alias name>
PIVOT
(
<aggregation function>(<column name>)
FOR
[<column name that become column headers>]
IN ( [list of pivoted columns])
) AS <alias name for pivot table>

Let us take some examples.

Example 1

SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
ORDER BY [Tab2].[Year]

Output:

In above query we calculate the sum of sales for Pankaj, Rahul and Sandeep employee corresponding to year value.

Example 2

SELECT Name, 2010,2011,2012 FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR [Year] IN (2010,2011,2012)) AS Tab2
ORDER BY Tab2.Name

Output:

When we execute 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:

SELECT Name, [2010],[2011],[2012] FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR [Year] IN ([2010],[2011],[2012])) AS Tab2
ORDER BY Tab2.Name

Output

Example 3

In previous examples we wrote the name of pivot column. This approach is useful if we know all possible values for pivot column. But it is not fix that column always remain same, pivot column may be increased or decreased in future.

Let us take previous example. In previous example we wrote 2010,2011 and 2012 as pivot column. But it is not fix that these column will not change in future , so what should we do if there is a possibility that column number may change in future.

In such a condition we should use dynamic query. Firstly, retrieve all unique values from pivot column and after that write a dynamic query to execute it with pivot query at run time.

Now we execute example 2, but using dynamic query.

/*Declare Variable*/
DECLARE @Pivot_Column [nvarchar](max);
DECLARE @Query [nvarchar](max);
/*Select Pivot Column*/
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(Year) FROM
(SELECT DISTINCT [Year] FROM Employee)Tab
/*Create Dynamic Query*/
SELECT @Query='SELECT Name, '+@Pivot_Column+'FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR [Year] IN ('+@Pivot_Column+')) AS Tab2
ORDER BY Tab2.Name'
/*Execute Query*/
EXEC sp_executesql @Query

Output

UNPIVOT

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

DECLARE @Tab TABLE
(
[Year] int,
Pankaj int,
Rahul int,
Sandeep int
)

Insert Value in Temp Variable

INSERT INTO @Tab
SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
ORDER BY [Tab2].[Year]

Perform UNPIVOT Operation

SELECT Name,[Year] , Sales FROM @Tab t
UNPIVOT
(
Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Output

We can perform first PIVOT operation and after that UNPIVOT operation on same table in single query as in the following code snippet.

SELECT Name,[Year] , Sales FROM
(
SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
)Tab
UNPIVOT
(
Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Output

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 Merger Multiple Row) :

Now we perform PIVOT and UNPIVOT operation for this table and compare the resultant table from this table.

SELECT Name,[Year] , Sales FROM
(
SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
)Tab
UNPIVOT
(
Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Output

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

Case 2(PIVOT doesn’t Merger Multiple Row)

Now we perform PIVOT and UNPIVOT operation for this table and compare the resultant table from this table.

SELECT Name,[Year] , Sales FROM
(
SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
)Tab
UNPIVOT
(
Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Output

We can see that both the tables are same.

Conclusion

Today, we read PIVOT and UNPIVOT relational operator 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, it might or might not be exactly the same.