UNPIVOT in SQL Server

Suggested Videos
Part 116 - Window functions in SQL Server
Part 117 - Difference between rows and range
Part 118 - LAST_VALUE function in SQL Server



In this video we will discuss UNPIVOT operator in SQL Server. 



PIVOT operator turns ROWS into COLUMNS, where as UNPIVOT turns COLUMNS into ROWS.

We discussed PIVOT operator in Part 54 of SQL Server tutorial. Please watch Part 54 before proceeding.

Let us understand UNPIVOT with an example. We will use the following tblProductSales table in this demo.



SQL Script to create tblProductSales table 

Create Table tblProductSales

(

 SalesAgent nvarchar(50),

 India int,

 US int,

 UK int

)

Go

Insert into tblProductSales values ('David', 960, 520, 360)

Insert into tblProductSales values ('John', 970, 540, 800)

Go

Write a query to turn COLUMNS into ROWS. The result of the query should be as shown below.


SELECT SalesAgent, Country, SalesAmount

FROM tblProductSales

UNPIVOT

(

       SalesAmount

       FOR Country IN (India, US ,UK)

) AS UnpivotExample