Adsense

Friday, February 22, 2013

PIVOT and UNPIVOT in SQL SERVER

PIVOT and UNPIVOT Operators has been introduced with SQL SERVER 2005 onwards. These two operators in simplest terms can be explained as extension of grouping functionality. Using Group by clause we can perform aggregation or grouping of rows, but in some real time business scenarios specially while generating SQL based reports, we might have move rows values to columns or vice verse. Hence to make it simple PIVOT and UNPIVOT operators were introduced.

PIVOT
Use this when you want to turn the rows values to columns.

UNPIVOT
Use this when you want to turn your column values to rows.



Following example can elaborate these operators in detail..

Lets start with UNPIVOT ==> Column values to rows.

here is an example, we have a CountryStatus table which shows SalesRevenue  count for INDIA, DUBAI, USA and UK in there respective columns. Following is a script to create schema for the table with some sample data.

Create table CountryStatus
(
ProductID int,
India int,
Dubai int,
USA int,
UK int
)

Insert into CountryStatus
Select 1, 2000, 6132, 154512, 0211652 union
Select 2, 9585, 02144, 1454, 116165 union
Select 3, 21574, 3000, 15000, 1211 union
Select 4, 5487, 3000, 1545, 25254 union
Select 5, 4875, 3000, 15000, 4542 union
Select 6, 121654, 3000, 15000, 20000 union
Select 7, 7545, 3000, 15486, 45415 union
Select 8, 15465, 3000, 144, 20000 union
Select 9, 156, 3000, 154, 15154 union
Select 10, 79789, 3000, 1424, 115 

After inserting data into the table, lets try to convert the Country wise sales review from Columns to rows ==> UNPIVOT. Here is  a query for the same.

Notice that Country and SalesRevenue columns are not present in Source derived table, how is that ?? the Fact is even though SalesRevenue and Country column was not present in the source, but the information was there with different label or divided across country named columns, and that's what UNPIVOT do, it allows us to move the column names to row values, so now the Country names are going to be shown in one column, hence we need another column to hold SalesRevenue information.
Select ProductID, Country, SalesRevenue
From
(
        Select ProductID, India, Dubai, USA, UK from    
        CountryStatus
) Source

UnPIVOT
(
SalesRevenue
for Country in ([India],[Dubai],[USA],[UK])
)unpvt

Here is the output of the query.



PIVOT Example: Rows to Columns

In this example consider above query as our source and we will query this source to generate result set which is similar to our table structure CountryStatus.

Here is a query..

Select ProductID, [India],[Dubai],[USA],[UK] from 
(
         Select ProductID, Country, SalesRevenue
         From
         (
              Select ProductID, India, Dubai, USA, UK from               
              CountryStatus
          ) P
         UnPIVOT
           (
        SalesRevenue
        for Country in ([India],[Dubai],[USA],[UK])
           )unpvt
) Source
PIVOT
(
Avg(SalesREvenue)
for
Country in ([India],[Dubai],[USA],[UK])
)PVT




You might have noticed in both the above examples that untill unless you know the data you cannot write queries with PIVOT or UNPIVOT Operators, and many times we land into a situation where we need to perform queries on thousands or millions of records. In the above example we have only 4 Countries in our table named countryStatus, but what if we have States or Cities instead of countries, there can be 100s of Cities and in that case writing each and every city name in the pivot may not be possible, hence in that case we use dynamic sql to generate queries, and then execute them using exec command.

In this Article our intention is to share just basic of PIVOT and UNPIVOT, we hope these examples might have provided our viewers some understanding on PIVOT and UNPIVOT Operator.

For more articles on T-SQL click here

2 comments :