UNPIVOT in SQL Server

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