menu search more_vert
Home DBMS SQL Server PIVOT and UNPIVOT in SQL Server with Example

PIVOT and UNPIVOT in SQL Server with Example

watch_later 18 May, 2019
comment Add Comment

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

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.

comment 0 comments:

more_vert

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon

expand_less