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