SQL Query Column Dynamic Alias Name Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query Column Dynamic Alias Name Issue

Author

Topic

sreeni2219
Starting Member

2 Posts

Posted - 2010-02-08 : 19:02:17

I 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 ' + FiscalYear
or ' Year ' + year(DATEADD(year,-1,getdate())). Please help me to resolved this issue. if possible
i 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 Year5
from TRANS I
where 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 FiscalYear
ORDER BY FiscalYear

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-08 : 19:16:17

You could use dynamic SQL to define the column names, too, but why would you want to?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

sreeni2219
Starting Member

2 Posts

Posted - 2010-02-08 : 20:12:31

I want to display in the Report, But that tool won't support any kind of formats, So my SQl should handel everything

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources