Concatenated string in column alias

Question

I have some what strange requirement. I need concatenated string as column alias in ms-sql.

For. Eg.,

Problem :

@declare @columnName as nvarchar(10)='2015'    
select 1 as [Rank in @columnName]

Expected output :

------------------
|  Rank in 2015  |
------------------
|       1        |
------------------

EDIT: I would be happy not to do it with dynamic SQL, if it's not the only option

EDIT:

The reason I'm doing this from sql is i don't have front end as such.

We actually are providing data to users, which again are front end developers, they may then consume that data and build their own interface, considering that in mind i wanted to publish dataset which has meaning in itself without having to mention it explicitly.

I hope you got the idea.

Answer 1

The following is a reasonable solution:

declare @columnName nvarchar(10) = '2015';
declare @sql nvarchar(max) = 'select @value as ' + quotename('Rank in ' + columnName);

exec sp_executesql @sql, N'@value int', @value = @value;

A bit longer discussion. If you are going to have the new column name based on an existing column name, then you will need to modify more things in the query. For dynamic SQL, I highly recommend that you get used to sp_executesql over just exec -- it allows you to pass parameters in and out of the execution environment. In addition to being useful, this helps guard against SQL injection attacks.

That said, I think you should give the columns generic names and handle the column naming in whatever application is running the query. It sounds like the choice of column headers is for satisfying some users. They may prove fickle and change their mind, or different users might want different column headers. Handling this at the application layer means that such changes do not require changing SQL code. And, more localized changes are safer in the long term.

Answer 2

You can do It in following, but it's dynamic SQL:

DECLARE @columnName NVARCHAR(10) = '2015]'
EXEC ('SELECT ''1'' AS [Rank in ' + @columnName)