title: Flattening of a 1 row table into a key-value pair table
body: <p>What's the best way to get a key-value pair result set that represents column-value in a row?</p>
  
  <p>Given the following table A with only 1 row</p>
  
  <pre><code>
  <strong>Column1 Column2 Column3 </strong>...
  Value1  Value2  Value3
  </code></pre>
  
  <p>I want to query it and insert into another table B:</p>
  
  <pre><code>
  <strong>Key                  Value</strong>
  Column1              Value1
  Column2              Value2
  Column3              Value3
  </code></pre>
  
  <p>A set of columns in table A is not known in advance.</p>
  
  <p>NOTE: I was looking at FOR XML and PIVOT features as well as dynamic SQL to do something like this:</p>
  
  <pre><code>
      DECLARE @sql nvarchar(max)
      SET @sql = (SELECT STUFF((SELECT ',' + column_name 
                                FROM INFORMATION_SCHEMA.COLUMNS 
                                WHERE table_name='TableA' 
                                ORDER BY column_name FOR XML PATH('')), 1, 1, ''))
      SET @sql = 'SELECT ' + @sql + ' FROM TableA'
      EXEC(@sql)
  </code></pre>