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

Labels: