Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.

unpivot in sql server example

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.
sql server unpivot example

SELECT SalesAgent, Country, SalesAmount
FROM tblProductSales
UNPIVOT
(
       SalesAmount
       FOR Country IN (India, US ,UK)
) AS UnpivotExample

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.