Supercharge Your Database: How Pivot in SQL Can Help You

Learn how to use Pivot in SQL and Unpivot in SQL. Pivot and Unpivot in SQL are two relational operators that are used to convert a table expression into another. Pivot in SQL is used when we want to transfer data from the row level to the column level and Unpivot in SQL is used when we want to convert data from the column level to the row level. PIVOT and UNPIVOT relational operators are used to generate 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 combine and compares a 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.

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 the data of the Employee table.

Output

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

PIVOT in SQL

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 the PIVOT operator, we can perform an aggregate operations where we need them.

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

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 the above query, SQL Server throws an error because we can’t provide an 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 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 columns changes in the database?

Let us take the previous example. In the previous example, we used 2010, 2011, and 2012 as pivot columns. But it is not fixed that this column will not change in the future. What happens when we get data from the 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 a pivot query at run time.

Now we can execute example 2, but using the 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 in SQL

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

Example 4

Suppose the output of example 2 is stored in a Temp variable. Now we want to rotate column identifiers Pankaj, Sandeep, and 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 the first PIVOT operation and after that UNPIVOT operation on the same table in a 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 the conclusion is that if the PIVOT operation merges multiple rows in a single row, then UNPIVOT operation can’t retrieve the original table from the output of the PIVOT operation. But if the PIVOT operation doesn’t merge multiple rows in a single row, then UNPIVOT operation can retrieve the original table from the output of the PIVOT operation.

Let us take an example,

Case 1. PIVOT merges multiple rows

Now we perform PIVOT and UNPIVOT operations 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 the same. The 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) rows in a single row. That's why the UNPIVOT operation can’t retrieve the original table.

Case 2. PIVOT doesn’t merge multiple rows

Now let's perform PIVOT and UNPIVOT operations for this table and compare the result of the new table with 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 the 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.

Are you ready to supercharge your database? With Pivot in SQL Server, you can take your data to the next level. Pivot offers fast and efficient ways to query and manipulate data, allowing you to make decisions quickly and accurately. By mastering Pivot, you'll gain the skills to efficiently manage and analyze data, giving you the power to make smarter, data-driven decisions. So what are you waiting for? Take the plunge and unlock the potential of Pivot in SQL Server today!