PIVOT and UNPIVOT in SQL Server with Example

watch_later Saturday, May 18, 2019

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.

PIVOT and UNPIVOT in SQL Server with Example
Figure: 1.0

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

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

SQL Server PIVOT
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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon