3/06/2014

T-SQL CTE to UnPivot

Here' a query I wrote, which uses a T-SQL CTE to Un-Pivot some raw data brought in from Excel.

I'm basically posted for reference purposes so I can reference at a later point in time.



WITH RevBudgetData_CTE
As
(
SELECT
       [Code]
       ,Months
       ,MonthlyAmount   
       FROM
       (
SELECT [Code]
      ,[July2013] as [July2013]
      ,[August2013] as [August2013]
      ,[September2013] as [September2013]
      ,[October2013] as [October2013]
      ,[November2013] as [November2013]
      ,[December2013] as [December2013]
      ,[January2014] as [January2014]
      ,[February2014] as [February2014]
      ,[March2014] as [March2014]
      ,[April2014] as [April2014]
      ,[May2014] as [May2014]
      ,[June2014] as [June2014]
  FROM [dbo].[Rev Budget Import]
) UNP
UNPIVOT
( MonthlyAmount FOR Months IN
       ([July2013]
      ,[August2013]
      ,[September2013]
      ,[October2013]
      ,[November2013]
      ,[December2013]
      ,[January2014]
      ,[February2014]
      ,[March2014]
      ,[April2014]
      ,[May2014]
      ,[June2014]
       )
) as unpvt
)
select
       S.StationSK as StationSk
       ,d.dateSK AS BudgetDateSK
       ,MonthlyAmount
 FROM RevBudgetData_CTE c
 inner join DimDate d
 on d.Date_Date = CONVERT(DATE,c.[Months])
  inner join DimStation s
 on s.StationAK = c.[Code]

No comments:

Post a Comment

Babalon