将 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屋！