CODEWITHSUNDEEP
sql-servert-sqlrename
user918967
user918967

Reputation: 2167

How do I rename a column based on a variable

I want to rename a column at the end of a series of queries based on a variable. It would perform something like this:

 DECLARE @formTable TABLE (FormID varchar(max))
    INSERT @formTable SELECT 'Form1'AS FormID;
    INSERT @formTable SELECT 'Form2'AS FormID;
    INSERT @formTable SELECT 'Form3' AS FormID;
    INSERT @formTable SELECT 'Form5'     AS FormID;


 DECLARE @CCount TABLE (FormID varchar(max), Present varchar(max))
    INSERT @CCount SELECT 'Form1' AS FormID, 'Yes' AS Present;
    INSERT @CCount SELECT 'Form2' AS FormID, 'Yes' AS Present;

 DECLARE @subject varchar(12);
 SET @subject = 'Subject 22'

 SELECT t.FormID AS Form, Present  
 FROM  @formTable t LEFT JOIN @CCount c ON t.FormID = c.FormID

which creates a table that looks like this:

 Form     Present
 Form1      Yes
 Form2      Yes
 Form3      NULL
 Form5      NULL

I want it to look like this :

  Form     Subject 22
  Form1      Yes
  Form2      Yes
  Form3      NULL
  Form5      NULL

How can I accomplish this ?

Thanks !

Upvotes: 2

Views: 7300

Answers (2)

Andomar
Andomar

Reputation: 238086

One way would be dynamic SQL:

declare @sql nvarchar(max)
set @sql = 'SELECT t.FormID AS Form, Present as [' + @subject + 
    '] FROM  @formTable t LEFT JOIN @CCount c ON t.FormID = c.FormID'
exec @sql

Upvotes: 0

Oded
Oded

Reputation: 499002

You can't use variables for column names - you can do this using dynamic SQL, though you need to be very careful with dynamic SQL and not introduce a SQL injection vulnerability.

Upvotes: 2

Related Questions