Introduction
This article gives an explanation about the pivot and unpivots operators in SQL server with example. The process of converting rows into columns called as PIVOT or CROSS TAB and the process of converting columns into rows called UNPIVOT. This is may be one of the common requirements for all of us who work on data-driven applications. SQL Server 2005 introduced two new operators first is PIVOT and second is UNPIVOT that made writing queries easier.
 |
| Figure: 1.0 |
In my previous articles, I explained How to Split Comma Separated String in SQL Server and Export JSON Data to Excel/CSV File using AngularJs With Bootstrap and Export Dataset/Datatable to CSV File Using C# and VB.NET and Bootstrap JQuery Multiselect Dropdown List With Checkbox in ASP.NET Using C# And VB.NET and How to Read CSV File In ASP.NET Webforms With Example and How to Create Missing Index From Actual Execution Plan in SQL Server and in this article I am going to explain PIVOT and UNPIVOT in SQL Server with Example.
Requirement
1) What is PIVOT in SQL server?
2) What is UNPIVOT in SQL server?
3) Explain PIVOT and UNPIVOT in SQL server with example.
What is PIVOT in SQL server?
Pivot is a relational operation available in SQL server, which allows users to convert row-level data to the column level. In other words, we can say that PIVOT rotated the tables based on their unique values, mostly PIVOT used with aggregation functions such as MIN, MAX, SUM, COUNT and etc.
What is UNPIVOT in SQL server?
As the PIVOT converts tables rows into columns UNPIVOT exactly does the reverse. UNPIVOT operates on an already pivoted resultset in terms of retry original resultset. In UNPIVOT statement you have to specify two columns first columns contains the value from the no of pivoted columns and the second column includes the name of pivoted columns.
So, now let's take one example that clearly explains how you can convert row-level data to the column level and how you can convert column level to the row-level data using pivot and unpivots. Here we will take a small example of a sales and inventory management system where we will display product wise sales based on the year.
Example
For, demonstration we will declare a temp table and insert some dummy records for the demonstration purpose.
PIVOT
Declare Temp Table
DECLARE @TableSales AS TABLE ( ProductName VARCHAR(50), TotalSales FLOAT, YearName INT )
Insert Records into Table
INSERT INTO @TableSales (ProductName,TotalSales,YearName) VALUES -- Total Sales in Year 2016 ('Shampoo' ,200010,2016), ('TV' ,300500,2016), ('AC' ,100054,2016), ('Fridge',3097540,2016), ('Washing Machine',289635,2016), ('Mobile',29600,2016), -- Total Sales in Year 2017 ('Shampoo' ,20440,2017), ('TV' ,963400,2017), ('AC' ,58963,2017), ('Fridge',475693,2017), ('Washing Machine',556936,2017), ('Mobile',90634,2017), -- Total Sales in Year 2018 ('Shampoo' ,95415,2018), ('TV' ,478952,2018), ('AC' ,225688,2018), ('Fridge',945630,2018), ('Washing Machine',796500,2018), ('Mobile',659803,2018), -- Total Sales in Year 2019 ('Shampoo' ,95415,2019), ('TV' ,478952,2019), ('AC' ,225688,2019), ('Fridge',945630,2019), ('Washing Machine',796500,2019), ('Mobile',659803,2019)
Select Records From Table
SELECT * FROM @TableSales ORDER BY ProductName
Result of Query
 |
| Figure: 1.1 |
If you analyzed the above result set then there are 6 different products are there and total sales of each item are year wise different, and all the year is product wise duplicate because it is in the form of rows so it is somehow complex to get year wise sales based on the year so we will convert all the years into columns using PIVOT as I showed in the example below.
PIVOT Query
SELECT ProductName, [2016],[2017],[2018],[2019] FROM ( SELECT ProductName,TotalSales,YearName FROM @TableSales) TableSales PIVOT (SUM(TotalSales) FOR YearName IN ([2016],[2017],[2018],[2019])) AS pvt ORDER BY ProductName
Result of PIVOT Query
 |
| Figure: 1.2 |
Explanation
If you analyzed the above PIVOT query then I have selected ProductName, TotalSales, YearName from table @TableSales and give alias of this table as TableSales and then put this query as subquery and PIVOT this result set using PIVOT operator and also used aggregate function SUM for TotalSales column for YearName in [2016],[2017],[2018] and [2019] and finally select columns name ProductName, [2016],[2017],[2018],[2019] order by ProductName.
NOTE: If using the PIVOT operator then you must need to use an aggregate function like MIN, MAX, COUNT, SUM and etc.
UNPIVOT
Declare Temp Table
DECLARE @TableSales AS TABLE ( ProductName VARCHAR(50), [2016] FLOAT, [2017] FLOAT, [2018] FLOAT, [2019] FLOAT )
Insert Records into Table
INSERT INTO @TableSales (ProductName,[2016],[2017],[2018],[2019]) VALUES ('AC',100054,58963,225688,225688), ('Fridge',3097540,475693,945630,945630), ('Mobile',29600,90634,659803,659803), ('Shampoo',200010,20440,95415,95415), ('TV',300500,963400,478952,478952), ('Washing Machine',289635,556936,796500,796500)
Select Records From Table
SELECT * FROM @TableSales ORDER BY ProductName
Result of Query
The result of the query is the same as the query result shown in Figure: 1.2.
Now, let's write a query to unpivot records and get the original unpivoted resultset from the pivoted table.
UNPIVOT Query
SELECT ProductName, TotalSales ,YearName FROM @TableSales UNPIVOT ( TotalSales FOR YearName in ([2016],[2017],[2018],[2019]) ) AS UnpivotSales
Result of UNPIVOT Query
The result of the query is the same as the query result shown in Figure: 1.1.
Explanation
If you analyzed the above UNPIVOT query then I have selected ProductName, TotalSales, YearName from the table @TableSales and using the UNPIVOT operator I have converted columns area into a row area for YearName column and give an alias to this column as YearName and then UNPIVOT TotalSales for YearName in [2016],[2017],[2018] and [2019]. Basically, this is the reverse process of PIVOT.
This is very basic examples for PIVOT and UNPIVOT, here we also can PIVOT and UNPIVOT for multiple columns that we will learn in the next article where I will show you how you can PIVOT and UNPIVOT more than one columns in SQL server, still If you have any questions regarding PIVOT and UNPIVOT in QL server then you can write your questions in the comments section that is given below the article.
Summary
In this article, we learned how to convert row-level data to the column level using the PIVOT operator as well as how to convert column level data to the row level using the UNPIVOT operator with a simple example.
