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)

Solution 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

Solution 2

I think you're halfway there. Just use UNPIVOT and dynamic SQL as Martin recommended:

CREATE TABLE TableA (
  Code VARCHAR(10),
  Name VARCHAR(10),
  Details VARCHAR(10)
) 

INSERT TableA VALUES ('Foo', 'Bar', 'Baz') 
GO

DECLARE @sql nvarchar(max)
SET @sql = (SELECT STUFF((SELECT ',' + column_name 
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))

SET @sql = N'SELECT [Key], Val FROM (SELECT ' + @sql + ' FROM TableA) x '
+ 'UNPIVOT ( Val FOR [Key] IN (' + @sql + ')) AS unpiv'
EXEC (@sql)

Results:

Key          Val
------------ ------------
Code         Foo
Name         Bar
Details      Baz

There is a caveat, of course. All your columns will need to be the same data type for the above code to work. If they are not, you will get this error:

Msg 8167, Level 16, State 1, Line 1
The type of column "Col" conflicts with the type of 
other columns specified in the UNPIVOT list.

In order to get around this, you'll need to create two column string statements. One to get the columns and one to cast them all as the data type for your Val column.

For multiple column types:

CREATE TABLE TableA (
  Code INT,
  Name VARCHAR(10),
  Details VARCHAR(10)
) 

INSERT TableA VALUES (1, 'Foo', 'Baf') 
GO

DECLARE 
  @sql nvarchar(max),
  @cols nvarchar(max),
  @conv nvarchar(max) 

SET @cols = (SELECT STUFF((SELECT ',' + column_name 
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))

SET @conv = (SELECT STUFF((SELECT ', CONVERT(VARCHAR(50), ' 
                          + column_name + ') AS ' + column_name
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE table_name='TableA' 
                          ORDER BY ordinal_position FOR XML PATH('')), 1, 1, ''))


SET @sql = N'SELECT [Key], Val FROM (SELECT ' + @conv + ' FROM TableA) x '
+ 'UNPIVOT ( Val FOR [Key] IN (' + @cols + ')) AS unpiv'
EXEC (@sql)

Solution 3

Perhaps you're making this more complicated than it needs to be. Partly because I couldn't wrap my little brain around the number of PIVOT/UNPIVOT/whatever combinations and a dynamic SQL "sea of red" would be necessary to pull this off. Since you know the table has exactly one row, pulling the value for each column can just be a subquery as part of a set of UNIONed queries.

DECLARE @sql NVARCHAR(MAX) = N'INSERT dbo.B([Key], Value) '

SELECT @sql += CHAR(13) + CHAR(10) 
        + ' SELECT [Key] = ''' + REPLACE(name, '''', '''''') + ''', 
        Value = (SELECT ' + QUOTENAME(name) + ' FROM dbo.A) UNION ALL'
FROM sys.columns 
WHERE [object_id] = OBJECT_ID('dbo.A');

SET @sql = LEFT(@sql, LEN(@sql)-9) + ';';

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (I only created 4 columns, but this would work for any number):

INSERT dbo.B([Key], Value)
 SELECT [Key] = 'Column1', 
        Value = (SELECT [Column1] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column2', 
        Value = (SELECT [Column2] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column3', 
        Value = (SELECT [Column3] FROM dbo.A) UNION ALL
 SELECT [Key] = 'Column4', 
        Value = (SELECT [Column4] FROM dbo.A);

The most efficient thing in the world? Likely not. But again, for a one-row table, and hopefully a one-off task, I think it will work just fine. Just watch out for column names that contain apostrophes, if you allow those things in your shop...

EDIT sorry, couldn't leave it that way. Now it will handle apostrophes in column names and other sub-optimal naming choices.