title: TSQL - Dynamic Column Name in Dynamic SQL?
body: <p>I have a "table A" with the following structure:</p>
  
  <pre><code>Act_Code  ACT_TYPE_1  ACT_TYPE_2   ACT_TYPE_3  ACT_TYPE_4
  --------  --------   -----------   ----------  -----------
  ACT1       A            NULL        NULL         NULL
  ACT2       NULL         B           NULL         NULL
  ACT3       NULL         NULL        C            NULL
  ACT4       NULL         NULL        NULL         D
  ACT1       A            NULL        NULL         NULL
  </code></pre>
  
  <p>As you can see, the ACT_TYPE data will always store into the field name which refers to the last digit of "Act_Code" fields:
  Eg.
  When Act_Code = "ACT1", the Act_Type is stored in field "ACT_TYPE_1"
  When Act_Code = "ACT2", the Act_Type is stored in field "ACT_TYPE_2"
  and so on...</p>
  
  <p>Now, I want to read the data from the above "Table A" and insert into "Table B" which is having the following structure:</p>
  
  <pre><code>ACT    ACT_TYPE
  ----   --------
  1        A
  2        B
  3        C
  4        D
  </code></pre>
  
  <p><strong>Question:</strong>
  *<em>1. How can I add "dynamic column name" inside a select query?</em>*</p>
  
  <p>For example, </p>
  
  <pre><code>INSERT INTO Table_B (ACT, ACT_TYPE)
  SELECT RIGHT(Act_Code,1), ## FROM Table_A
  </code></pre>
  
  <p><strong>How can I handle the dynamic column name as per the symbol "##" above?</strong></p>
  
  <p>I've tried:</p>
  
  <pre><code>SET @sql = 'INSERT INTO Table_B (ACT,ACT_TYPE) '
  SET @sql = @sql + 'SELECT RIGHT(Act_Code,1), '
  SET @sql = @sql + '''ACT_TYPE_'' + RIGHT(Act_Code,1) FROM Table_A'
  EXEC (@sql)
  </code></pre>
  
  <p>But it doesn't work!</p>
  
  <p>Please help, thanks very much!</p>