Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl

When dealing with importing Excel spreadsheets and cross-tab reports, UNPIVOT is your friend.

This will translate a column-based date data format to a row-based format which can be used in PerformancePoint.

create view vActualsByCostCentre_Unpivot
as
SELECT [CostCentre] as [CostCentreLabel]
      ,[Label] as [AccountLabel]
      ,[MemberID]
      ,[Value]
FROM
(
SELECT [CostCentre]
      ,[Label]
      ,[20070701] = [Jul07]
      ,[20070801] = [Aug07]
      ,[20070901] = [Sept07]
      ,[20071001] = [Oct07]
      ,[20071101] = [Nov07]
      ,[20071201] = [Dec07]
      ,[20080101] = [Jan08]
      ,[20080201] = [Feb08]
      ,[20080301] = [Mar08]
      ,[20080401] = [Apr08]
FROM [ActualsByCostCentre]) [Value]
UNPIVOT ([Value]
    FOR [MemberID] in
      ([20070701]
      ,[20070801]
      ,[20070901]
      ,[20071001]
      ,[20071101]
      ,[20071201]
      ,[20080101]
      ,[20080201]
      ,[20080301]
      ,[20080401])
  ) AS UNPVT

Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl