PIVOT and UNPIVOT in SQL Server with Example

This article gives an explanation of the pivot and unpivots operators in SQL servers with examples. The process of converting rows into columns is called PIVOT or CROSS TAB and the process of converting columns into rows is called UNPIVOT. This 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 which made writing queries easier.

Figure: 1.0

Requirement 

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 retrying the original resultset. In UNPIVOT statement you have to specify two columns first column contains the value from the no of pivoted columns and the second column includes the name of pivoted columns.

Example

For, the 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

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

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

SELECT ProductName, TotalSales ,YearName
FROM @TableSales
UNPIVOT
(
 TotalSales
 FOR YearName in ([2016],[2017],[2018],[2019])
) AS UnpivotSales

Explanation

Summary