Home DBMS SQL Server PIVOT and UNPIVOT in SQL Server with Example

PIVOT and UNPIVOT in SQL Server with Example

watch_later 5/18/2019
comment Add Comment

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.


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 examples.

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.

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

Query Result
Figure: 1.1

If you analyzed the above result set then there are 6 different products are there and the 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, and YearName from table @TableSales and given an 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.

These are very basic examples of PIVOT and UNPIVOT, here we also can PIVOT and UNPIVOT for multiple columns we will learn in the next article where I will show you how you can PIVOT and UNPIVOT more than one column in an 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