Link to home
Start Free TrialLog in
Avatar of sbornstein2
sbornstein2

asked on

SQL Query Pivot column help SQL 2012 Need Guru

Hello all,

Okay hoping to find an example on how to accomplish this.   So I have a table called YearlyAnalysis that has the following columns:

CustomerID
Year
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

Sample there are other columns this is good enough.   So what I need to do is take the current Year (getdate()) and go back 3 years.   What I am trying to get is columns such as this let's say we are in 2013 right so this:

                                  2010       2011      2012     2013
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

If the year does not exist I still want a column for it with all nulls.  Can anyone provide me a sample way to do this?   I am using SQL Server 2012.
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sbornstein2
sbornstein2

ASKER

I think this might work I am having one problem so what I am trying to do is instead of hardcoding the years I am doing this and year is an int field I have

select CustomerID, 'Revenue' as Metric, YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE())  from
(SELECT CustomerID, Year, Revenue from pivot_example ) t
pivot (sum(Revenue)  for Year in (YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE()))) as pvt

It does not like the GETDATE at all.  Is there a way to do this?  This way my query will never need changing for the years.

Thanks
There's some bad news I'm afraid.

It is not possible to give those year columns names that change unless you use "dynamic sql". If you are prepared to have columns names like this:

Year -3   Year-2   Year-1   Current Year

Then you don't need "dynamic sql".

Do you absolutely need those column names to change?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
these were all helpful actually thanks all