Sohaib Akhtar Sohaib Akhtar - 5 months ago 10
SQL Question

How to set a column alias as the result of a SQL query?

I need to use result of a SQL query to set column aliases. Please see below script and the result of the script I need to use it as column aliases.

select
convert(varchar,DATEADD(month, -12, dateadd(d,-day(convert(date,dateadd(d,-(day(getdate())),getdate()))),convert(date,dateadd(d,+1-(day(getdate())),getdate())))),107),
convert(varchar,convert(date,dateadd(d,-day(convert(date,dateadd(d,-(day(getdate())),getdate()))),convert(date,dateadd(d,+1-(day(getdate())),getdate())))),107)


I need the answer for my question as soon as possible.

Answer

Two solutions are described in the following link: Column alias based on variable

First solution:

  1. Set the alias in a variable
  2. Define the query as a nvarchar containing a reference to the variable.
  3. Execute the query using sp_executesql

    SET @column_alias = 'new_title'
    SET @sql = 'SELECT keycol, datacol AS ' + @column_alias + ' FROM Foo'
    
    EXEC sp_executesql @sql
    

Second solution: Rename the column after the execution of the query

    INSERT INTO Results
    SELECT keycol, datacol
    FROM Foo

    EXEC sp_rename 'Results.datacol', @column_alias, 'COLUMN'