SQL Query Column Dynamic Alias Name Issue

• sreeni2219

  SSC Veteran

  Points: 227

  More actions

  February 8, 2010 at 5:06 pm

  #145116

  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

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  February 10, 2010 at 3:00 am

  #1116646

  I hope this will work for u

  select + 'YEAR' + CAST (year(DATEADD(year,-1,getdate())) AS VARCHAR(20))

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• wschampheleer

  SSCertifiable

  Points: 5504

  More actions

  February 10, 2010 at 3:18 am

  #1116651

  You could rewrite your query to return 1 row per year (instead of different columns) with one column that holds the year and one that holds the value.

  e.g.

  col1 col2 col3 year value

  xxx xxx xxx 2009 100.0

  xxx xxx xxx 2010 120.5

  Then use pivot to turn the rows into columns.

  e.g.

  col1 col2 col3 2009 2010

  xxx xxx xxx 100.0 120.5

  See BOL if you need more information.

  Regards,

  Willem
  http://wschampheleer.wordpress.com[/url]

• Kalaivani.Ayyasamy

  SSC Rookie

  Points: 41

  More actions

  September 21, 2011 at 3:12 am

  #1384375

  I need dynamic alias name to be generated each time when I execute the query . For eaxample

  select emp_name, salary as "SAL ON 2011" from emp;

  The year in the alias name "SAL ON 2011" ie., 2011 should be generated dynamically. I dont want PL/SQL, stored proc or function as a solution. I need to change in the query itself.

  Any suggestions?