SQL Server UNPIVOT and PIVOT Function in Data Visualization

We store data in a database so that we can analyze the data in the future to understand how our business is running. When we analyze the data, data must be in a proper format. You extract data from a database via a select query and paste it into Excel. Then you use different Excel tools to analyze data. Pivot and Unpivot in SQL Server are two of the most common tools used in Excel to analyze data.

These tools allow the data to be grouped horizontally and vertically. Over the following few paragraphs, we will learn to use two tools in SQL Server: Unpivot and pivot. But what if these same tools are available directly on SQL Server? Then you can directly format the data in SQL Server and provide the final output instead of downloading the raw data in Excel and applying the tools there. Understanding the SQL tools begins with understanding SQL servers; you can get an insight about the same through our online SQL server training.

What are Pivot and Unpivot in SQL Server?

PIVOT function in SQL carries out an aggregation and merges possible multiple rows into a single row in the output. UNPIVOT function in SQL doesn't reproduce the original table-valued expression result because rows have been merged. Also, null values in the input of UNPIVOT SQL Server disappear in the output.

Pivot and Unpivot in SQL Server are two relational operators used to convert a table expression into another. Pivot function in SQL is used when we want to transfer data from row level to column level, and Unpivot function in SQL is used to convert data from column level to row level. Let's dive further onto tools used in SQL and learn more about its importance in SQL and key takeaways. You should check out the SQL career path guide to help you explore all your career options. 

Let us now learn about Pivot and Unpivot in SQL Server in more detail. For that, we create a table called Employee. The query for the creation of table is as below.  

CREATE TABLE Employee   

   Name [nvarchar](max),  

   [Year] [int],  

   Sales [int]  

We now insert some 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   

The output of the table looks as below.

PIVOT 

PIVOT function in SQL relational operator converts data from row level to column level. PIVOT rotates a table-valued expression by turning the unique values from one column into multiple columns in the output. Using the PIVOT function in SQL, we can find our how to PIVOT in SQL and perform aggregate operations where needed.

The syntax is as below.

SELECT ,  
         
FROM  

Let us now look at some examples. The query for the first SQL PIVOT example is as below.

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]  

The Output Looks Like Below.

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

The second SQL PIVOT example is as below.

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

The Output Looks Like Below.

When we execute the above query, SQL Server throws an error because we can’t directly provide an integer value as a column name. 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  

The Output Looks Like Below.

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

We used 2010, 2011, and 2012 as pivot columns in the previous example. What happens when we get data from the year 2013?

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 let us execute the query below.

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

The Output Looks Like Below.

UNPIVOT 

The UNPIVOT function in SQL relational operator is the reverse process of the PIVOT relational operator. UNPIVOT relational operator converts data from the column level to the row level. Now we would check up on some practical SQL UNPIVOT examples.

Suppose that the output of SQL UNPIVOT 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 function in SQL Server.

Let us first declare a temp table.  

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

Let us then insert the value in the temp table.

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]  

Now let us perform the UNPIVOT operation.

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

The output looks like below.

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.

The query looks like this below.

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  

The Output is as Follows.

UNPIVOT operation is a reverse process of PIVOT operation, but UNPIVOT function in SQL Server is not the exact reverse of PIVOT in SQL Server. Suppose PIVOT performs an aggregation and merges multiple rows into a single row in the output. In that case, UNPIVOT in SQL Server 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.
 

Difference Between PIVOT and UNPIVOT Function in SQL Server

Advantages and Disadvantages of The PIVOT in SQL Server

We have learned the different aspects of using Pivot and Unpivot in SQL Server. Next, we will learn about the advantages and disadvantages of using Pivot table SQL Server.

Advantages of PIVOT in SQL Server

• Pivot in SQL Server allows you to see how your data works – Pivot tables are one of the many tools to help users get deeper insights into their data. You can create reports on multiple data sets from a single data pool.
• Works well with SQL exports – Many data in our business are often generated from SQL queries. Pivot tables can work directly with SQL exports (data downloaded/exported from SQL databases) to SQL servers. This makes it easier to harvest data and transfer it directly into a format you can quickly analyze.
• Large amounts of data can be segmented – One problem with data analysis is that it gets more complicated the more data you have. With pivot table SQL Server, you can easily segment data no matter how big the whole data set is. This makes analysis much more accessible and can help you spot trends in the data.
• Creating instant data is possible – When data is loaded into a pivot table, you can use it any way you want. You can even program equations directly into the pivot table or use formulas to create instant data.

Disadvantages of PIVOT in SQL Server 

• Mastering pivot tables takes time – Sure, creating a pivot table requires a few clicks inside Excel, but mastering the tool takes time. First-time users of pivot tables might see it as confusing and overwhelming. Only when you have “tamed the beast” can you properly use it for data analysis.
• It can be time-consuming to use – Depending on how you would like to use your data within the pivot table, it can take some time. The tool does not include a robust collection of calculation options. This means the user must manually calculate the data or input equations, which can take some time.
• There are no automatic updates – Unless you regularly update your pivot table with new data, you rely on old data for your metrics and analytics. This means relying on pivot tables for real-time analytics will be hard.
• Older computers might not be able to handle large data sets – When you are working with a couple of thousand lines of data, any computer will do just fine. But once you hit the tens of thousands mark, old computers might struggle to produce the needed data. It’s also not rare to see computers crash just because they can’t handle the amount of data they are processing.

Conclusion

Over the last few paragraphs, we have shown you how Pivot Unpivot in SQL Server works and their advantages and disadvantages. This will give you a fair idea about the topic and enough encouragement to study further on this topic. Or else you can also enroll in an online SQL server training course and can learn how to become a certified SQL professional. 

SQL Training For Administrators & Developers

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enroll for Demo Class