sql server - dynamic alias in T-SQL query

Question

I've got an issue generating an alias for a field in a query which for example gives me the revenue from last year and the year before. I was in the understanding that I could do something like :

SELECT 
    1234 AS 'REVENUE' + CAST (year(DATEADD(year,-1,getdate())) AS VARCHAR(20))
    4321 AS 'REVENUE' + CAST (year(DATEADD(year,-2,getdate())) AS VARCHAR(20))

But this doesn't work. Does somebody know how to get this done?

I want to end up with a table like

rownr|revenue2014|revenue2013
-----------------------------
1    |1234       |4321

Thanks a lot in advance!

greets Niels

Answer 1

DECLARE @sql VARCHAR(1000);
SET @sql = 'SELECT 
    1234 AS REVENUE' + CAST (year(DATEADD(year,-1,getdate())) AS VARCHAR(20)) + ',
    4321 AS REVENUE' + CAST (year(DATEADD(year,-2,getdate())) AS VARCHAR(20))
PRINT @sql
EXEC (@sql)

It is impossible to do that in the static SQL query. So only this way.