Column Alias Based on Variable

Although formatting output belongs to the reporting or front-end interface, occasionally there could be the need to change a column alias based on variable. Since T-SQL does not support proving variable as column alias in a query, here are two methods to handle that.

CREATE TABLE Foo (

 keycol INT PRIMARY KEY,

 datacol CHAR(1))

 

INSERT INTO Foo VALUES (1, 'a')

INSERT INTO Foo VALUES (2, 'b')

 

DECLARE @column_alias VARCHAR(30)

SET @column_alias = 'new_title'

 

-- 1). Using dynamic SQL

DECLARE @sql NVARCHAR(200)

 

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

 

EXEC sp_executesql @sql

 

-- 2). Using results table and renaming the column

CREATE TABLE Results (

 keycol INT PRIMARY KEY,

 datacol CHAR(1))

 

INSERT INTO Results

SELECT keycol, datacol

FROM Foo

 

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

 

SELECT * FROM Results

9 replies
  1. Anonymous
    Anonymous says:

    I adjusted the code to meet the needs of my query and I came up with this error:

    Msg 15248, Level 11, State 1, Procedure sp_rename,
    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

    Any ideas on how to correct this?

    Reply
  2. Anonymous
    Anonymous says:

    Yes. I won't post the entire main select because it is quite long (180 lines), but here is some of it. Hopefully this is enough:

    SELECT A1.lot_value AS Attribute1
    FROM ….

    DECLARE @column_rename VARCHAR(50)
    SET @column_rename = 'ZID47S'

    EXEC sp_rename '#Main.Attribute1', @column_rename, 'COLUMN'

    The main select creates #Main, and the alias of the column I want to change is Attribute1.

    Reply
  3. Developer
    Developer says:

    Hi I have a stored procedure and i want to use dynamic aliases it would be the current year. I searched and the suggested solution is dynamic sql only . please suggest as it is a long proc and making sql dynamic requires alot of declared variables that i have to like specify as '++' in this . please let me know if i can use dynamic aliases widout using dynamic sql

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.