sql - Flattening of a 1 row table into a key-value pair table

Question

What's the best way to get a key-value pair result set that represents column-value in a row?

Given the following table A with only 1 row

Column1 Column2 Column3 ...
Value1  Value2  Value3

I want to query it and insert into another table B:

Key                  Value
Column1              Value1
Column2              Value2
Column3              Value3

A set of columns in table A is not known in advance.

NOTE: I was looking at FOR XML and PIVOT features as well as dynamic SQL to do something like this:

    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)

Answer 1

A version where there is no dynamic involved. If you have column names that is invalid to use as element names in XML this will fail.

select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       T2.N.value('text()[1]', 'nvarchar(max)') as Value
from (select *
      from TableA
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

A working sample:

declare @T table
(
  Column1 varchar(10), 
  Column2 varchar(10), 
  Column3 varchar(10)
)

insert into @T values('V1','V2','V3')

select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       T2.N.value('text()[1]', 'nvarchar(max)') as Value
from (select *
      from @T
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

Result:

Key                  Value
-------------------- -----
Column1              V1
Column2              V2
Column3              V3

Update

For a query with more than one table you could use for xml auto to get the table names in the XML. Note, if you use alias for table names in the query you will get the alias instead.

select X2.N.value('local-name(..)', 'nvarchar(128)') as TableName,
       X2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
       X2.N.value('text()[1]', 'nvarchar(max)') as Value
from (
     -- Your query starts here
     select T1.T1ID,
            T1.T1Col,
            T2.T2ID,
            T2.T2Col
     from T1
       inner join T2
         on T1.T1ID = T2.T1ID
     -- Your query ends here
     for xml auto, elements, type     
     ) as X1(X)
  cross apply X1.X.nodes('//*[text()]') as X2(N)

SQL Fiddle