sql - 数据的组合半转置

Question

考虑以下查询：

DECLARE @T1 TABLE(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Data] VARCHAR(100),
  [Column1] VARCHAR(100),
  [Column2] VARCHAR(100),
  [Column3] VARCHAR(100));

INSERT INTO @T1([Data],[Column1],[Column2],[Column3])
    VALUES
        ('Data1','C11','C21','C31'),
        ('Data2','C12','C22','C32'),
        ('Data3','C13','C23','C33'),
        ('Data4','C14','C24','C34'),
        ('Data5','C15','C25','C35');

SELECT * FROM @T1;

输出如下所示：

现在我们要保留 Data 列，并为每个其他列堆叠选择该列的结果到最终表中。换句话说，以下查询产生输出：

-- I am looking for a better solution than below!
DECLARE @output TABLE([Data] VARCHAR(100),[Column] VARCHAR(100));

INSERT INTO @output([Data],[Column])
    (SELECT [Data],[Column1] FROM @T1
      UNION
    SELECT [Data],[Column2] FROM @T1
      UNION
    SELECT [Data],[Column3] FROM @T1)

SELECT * FROM @output

有什么比上面更清洁的方法来产生最终输出？随着列数的增加，这意味着对于每一个新列，我都需要一个单独的插入，这似乎是一个粗略的解决方案。理想情况下，我正在寻找基于枢轴的解决方案，但我无法提出具体的建议。

标签： sqlsql-servertsqlsql-server-2016sql-server-2017

Answer 1

当然，Yogesh 的解决方案会更高效。但是，由于您的列会随着时间的推移而扩展，因此这里有一种方法可以“动态地”在不实际使用 Dynamic SQ 的情况下对您的数据进行反透视：

例子

Select A.[Data]
      ,C.*
 From  @T1 A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(100)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)','varchar(100)') not in ('Id','Data','Other-Columns','To-Exclude')
             ) C

退货