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
Sweet, thanks,
THIS WORKED PERFECT! YOU ARE AWESOME! THANKS!
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?
Can you post the complete SQL after you made your adjustments? This will help to understand why you get the error.
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.
You have to invoke sp_rename from tempdb because this is a temp table:
EXEC tempdb..sp_rename '#Main.Attribute1', @column_rename, 'COLUMN';
That did it! Thanks!
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
Thank you soo much..it worked like a charm