Naming A column Dynamically

Question

Dear Experts

I have a simple query i.e. SELECT '100' as Sales2021,'200' as Sales2022

I want to name columns in above query dynamically as follows:-

SELECT
'100' as Concat('Sales',YEAR(GETDATE())-1)
,'200' as concat('Sales',YEAR(GETDATE()))

Is it possible? Is there any other way to achieve the above objective? Thanks in advance for your help.

Answer 1

Hi @Manish Papreja , welcome to Microsoft Q&A forum.

Please try below query:

DECLARE @sql VARCHAR(1000);  
SET @sql = 'SELECT   
    ''100'' AS ' + Concat('Sales',YEAR(GETDATE())-1) + ',  
    ''200'' AS ' + concat('Sales',YEAR(GETDATE()))  
PRINT @sql  
EXEC (@sql)  

Reference: dynamic alias in T-SQL query

Please let us know if this helps or else we can discuss further.

----------

If answer helps, you can mark it 'Accept Answer'

Answer 2

I want to name columns in above query dynamically as follows:-

With plain SQL it's not possible and don't make much sense; how should the client consuming the data should guess the column name?
That's not how SQL works.

Answer 3

Yes, you can do this, although it is a bit bulky. You need to create a temp table with fixed names. Then you can rename the columns with sp_rename.

I have an example here: https://www.sommarskog.se/dynamic_sql.html#dyncolaliases that you can work from.

In many cases, though, it is better to deal with this client side.