Monday, May 25, 2015

SQL/ tSql to UnPivot Pivoted Data on SQL Setver

You have to use SQL Server relational operator UNPIVOT/CROSS APPLY to UnPivot data from Pivoted data.

·        For single measure its simple to write unpivot query (tSql).
·        But for multiple measures, unpivot operator should have to be used for multiple times and a where clause required to relate each unpivor operator depends on your chosen common dimension.
Example:

v Single measure:

Ø  Sample Data - Table Name ([dbo].[TEST_PIVOT])
CITY
People – 2001
People – 2002
Dhaka
45623
524854
Dilly
456987
5478965
Ø  Sample Output
§  Here, CITY is your provided dimension and People & 2001/2002 is your unpivotable measure & dimension (period).
CITY
Period
People
Dhaka
2001
45623
Dhaka
2002
524854
Dilly
2001
456987
Dilly
2002
5478965
Ø  tSQL-
select CITY, Period, People
from ( select CITY, [People – 2001] as [2001], [People – 2002] as [2002] from [dbo].[TEST_PIVOT] ) Data
UNPIVOT (
People for Period IN ([2001], [ 2001])
) upvt

v Multi Measures:

Ø  Sample Data
CITY
People – 2001
People – 2002
Home – 2001
Home - 2002
Dhaka
45623
524854
4000
50000
Dilly
456987
5478965
40000
5000000
Ø  Sample Output
§  Here, CITY is your provided dimension and People & 2001/2002 is your unpivotable measure & dimension (period).
CITY
Period
People
Home
Dhaka
2001
45623
4000
Dhaka
2002
524854
50000
Dilly
2001
456987
40000
Dilly
2002
5478965
5000000
Ø  tSQL -

§  using UNPIVOT operator-

select CITY, PeriodPeople as Period, People, Home
from ( select CITY, [People – 2001], [People – 2002], [Home – 2001], [Home – 2002] from [dbo].[TEST_PIVOT] ) Data
UNPIVOT (
People for PeriodPeople IN ([People – 2001], [People – 2002])
) upvtP
UNPIVOT (
Home for PeriodHome IN ([Home – 2001], [Home – 2002] )
) upvtH
               WHERE RIGHT(upvtP.PeriodPeople,4) = RIGHT (upvtH.PeriodHome,4)


§  using CROSS APPLY operator

     SELECT [CITY]
, [PERIOD]
, [People]
, [Home]
FROM [dbo].[TEST_PIVOT]
CROSS APPLY (
VALUES (
[People-2001]
, [Home-2001]
, right([People-2001], 4)
)
, (
[People-2002]
, [Home-2002]
, right([People-2002], 4)
)
) a([People], [Home], [PERIOD])

No comments:

Post a Comment