|
TiaTia
Starting Member
1 Post |
 Posted - 2011-09-12 : 21:28:16
|
| Below is an original post from Sreeni2219. The only suggestion was to use dynamic sql. How would you use dynamic sql to solve the problem? Is there another method that could work? I'm very interested in the solution. Thanks for your help!Original post from 2010I have written a query which will return Rows as columns data for the past 5 years (dynamically). Query results looks good to me.I have column alias name Issue. Year1 really hold 2009 data (year(DATEADD(year,-1,getdate())))Now i want to display column names as 'Year 2009' instead of Year1. How to get the alias name.I don't want to hard code the year. I need column name like ' Year ' + FiscalYearor ' Year ' + year(DATEADD(year,-1,getdate())). Please help me to resolved this issue. if possiblei need to use SQl query instead of Stored procedure.SELECT FiscalYear,count(distinct AccountNumber) Members,SUM(CASE WHEN FiscalYear = year(DATEADD(year,-1,getdate())) THEN AMOUNT ELSE 0 END) AS Year1,SUM(CASE WHEN FiscalYear = year(DATEADD(year,-2,getdate())) THEN AMOUNT ELSE 0 END) AS Year2,SUM(CASE WHEN FiscalYear = year(DATEADD(year,-3,getdate())) THEN AMOUNT ELSE 0 END) AS Year3,SUM(CASE WHEN FiscalYear = year(DATEADD(year,-4,getdate())) THEN AMOUNT ELSE 0 END) AS Year4,SUM(CASE WHEN FiscalYear = year(DATEADD(year,-5,getdate())) THEN AMOUNT ELSE 0 END) AS Year5from TRANS Iwhere FiscalYear in (year(DATEADD(year,-1,getdate())),year(DATEADD(year,-2,getdate())) ,year(DATEADD(year,-3,getdate())),year(DATEADD(year,-4,getdate())),year(DATEADD(year,-5,getdate())))Group by FiscalYearORDER BY FiscalYear |
|
Dynamic Column alias with alpha and numeric data