将1行表展平为键值对表 [英] Flattening of a 1 row table into a key-value pair table

问题描述

获取键值对结果集以代表行中的列值的最佳方法是什么?

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

给出下表A仅1行

Column1 Column2 Column3 ...
Value1  Value2  Value3

我要查询它并插入到另一个表B中:

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

Key                  Value
Column1              Value1
Column2              Value2
Column3              Value3

表A中的一组列是预先未知的.

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

注意:我正在研究FOR XML和PIVOT功能以及动态SQL，以实现以下目的:

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)

推荐答案

一个不涉及动态的版本.如果您使用的列名称无效，不能用作XML中的元素名称，则会失败.

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)

工作示例:

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)

结果:

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

更新

对于具有多个表的查询，可以使用for xml auto来获取XML中的表名称.注意，如果您在查询中对表名使用别名，则会获取别名.

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小提琴

这篇关于将1行表展平为键值对表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！