Supercharge Your Database: How Pivot in SQL Can Help You

/*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!