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

no I just want it to say for example "REVENUE2014" and want that column to say "REVENUE2015" next year. — NCS
– NCS, Commented Apr 8, 2015 at 9:23
I think you have to use dynamic sql to achieve what you want, unless you can solve your original problem in some other way — Giorgos Betsos
– Giorgos Betsos, Commented Apr 8, 2015 at 9:26
Also, if this is being fed into something else (almost all queries are) such as a reporting tool or an application, performing the renaming there may be much easier than trying to do it in the SQL. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 8, 2015 at 9:32

Dmitrij Kultasev · Accepted Answer · 2015-04-08 09:30:14Z

6

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.

answered Apr 8, 2015 at 9:30

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sagar Over a year ago

Thanks Dmitrij , can I write dynamics SQL in Views , I want to have dynamics alias in my View , because my views are used by thereporter.

Dmitrij Kultasev Over a year ago

no, you can't have dynamic views. Usually report tools themselves can pivot data, so you can implement that logic there

Collectives™ on Stack Overflow

dynamic alias in T-SQL query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related