April 2, 2013 at 4:05 pm
I've successfully unpivoted Months01 through Months02 with a query that looks like this:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT
[FullAccount]
,[BusinessUnit]
,[ObjAcct]
,[Sub]
,[LT]
,[Century]
,[FY]
,Period
,sum(Qty) as Value
FROM
(SELECT [FullAccount]
,[BusinessUnit]
,[ObjAcct]
,[Sub]
,[LT]
,[Century]
,[FY]
,[Month01]
,[Month02]
,[Month03]
,[Month04]
,[Month05]
,[Month06]
,[Month07]
,[Month08]
,[Month09]
,[Month10]
,[Month11]
,[Month12]
,[Month13]
,[Month14]
,[Month15]
,[Month16]
,[Month17]
,[Month18]
,[Month19]
,[Month20]
,[Month21]
,[Month22]
,[Month23]
,[Month24]
,[FYPlus2]
,[FYPlus3]
,[FYPlus4]
,[Source]
,[Memo1]
,[Memo2]
,[lastchgby]
,[lastchgdate]
,[FiscalYear]
,[ForecastType]
,[CurrentForecast]
FROM [ForecastTemplate].[dbo].[Allocations]) T
unpivot
(Qty FOR Period IN
( [Month01]
,[Month02]
,[Month03]
,[Month04]
,[Month05]
,[Month06]
,[Month07]
,[Month08]
,[Month09]
,[Month10]
,[Month11]
,[Month12]
,[Month13]
,[Month14]
,[Month15]
,[Month16]
,[Month17]
,[Month18]
,[Month19]
,[Month20]
,[Month21]
,[Month22]
,[Month23]
,[Month24]
,[FYPlus2]
,[FYPlus3]
,[FYPlus4])
) as u
group by [FullAccount]
,[BusinessUnit]
,[ObjAcct]
,[Sub]
,[LT]
,[Century]
,[FY]
,Period
order by period
How can I rename the columns I am unpivoting on? I tried 'AS' statement in the unpivot portion but got an error....please see if you can offer suggestions without DDL. Otherwise, I will try to post DDL later. Thanks much
April 2, 2013 at 9:59 pm
I'll just make a temp table with the columns renamed, and un-pivot against the temp table instead of the original table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply