Monday, February 18, 2008

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

7 comments:

will said...

Sweet, thanks,

Anonymous said...

THIS WORKED PERFECT! YOU ARE AWESOME! THANKS!

Anonymous said...

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?

Plamen Ratchev said...

Can you post the complete SQL after you made your adjustments? This will help to understand why you get the error.

Anonymous said...

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.

Plamen Ratchev said...

You have to invoke sp_rename from tempdb because this is a temp table:

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

Anonymous said...

That did it! Thanks!