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).

o    A DynamicSP to UnPivot Pivoted Data of Single Measure.

·        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.

o   A Dynamic SP to UnPivot Pivoted Data of MultipleMeasures.

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])