Tomb_Raider_Legend Tomb_Raider_Legend - 6 months ago 12
SQL Question

SQL Server: how to set a column name as the value of a dynamic variable?

As the example below (maybe not a good example):

DECLARE @var1 VARCHAR(50)
SET @var1 = (SELECT TOP(1) MiddleName FROM myTable)

SELECT Name, SSN AS @var1
FROM myTable


What I am trying to do it set a value in a variable by using AS statement (in this case the first
MiddleName
value in the table
myTable
as the
SSN
column name). Any ideas on how to make this work?

Answer

You'll need dynamic SQL.

DECLARE @sqlText nvarchar(1000); 

DECLARE @var1 VARCHAR(50)
SET @var1 = (SELECT TOP(1) MiddleName FROM myTable)

SET @sqlText = N'SELECT Name, SSN AS ' + @var1 + ' FROM myTable'
Exec (@sqlText)
Comments