在SQL Server中转置或旋转表格而不使用GROUP BY

Question

在SQL Server中转置或旋转表格而不使用GROUP BY

3

我需要在SQL Server中对以下结构的表格进行透视操作：

CREATE TABLE table1 (
    ColumnNumber int,
    RowNumber int,
    CellData nvarchar(50)
)

INSERT INTO table1 VALUES
(1, 1, 'Orange'),
(2, 1, 'Apple'),
(3, 1, 'Banana'),
(1, 2, 'Grape'),
(2, 2, 'Corn'),
(3, 2, 'Lemon'),
(1, 3, 'Tomato'),
(2, 3, 'Lettuce'),
(3, 3, 'Onion')

我需要的结果表格应如下所示：

enter image description here

因此，ColumnNumber 行中的单元格现在是结果表格的列名。最困难的部分是不同列号的数量是可变的（因此现在我们有3个列号，但明天可能会有6或10个）。

我一直在查看 PIVOT 函数，但所有示例都包括一个 GROUP BY，正如您在这里看到的，我需要更像是“转置” Excel 函数的东西。

谢谢！

- Alvaro VS

动态构建SQL。是的，它很丑陋。但是，由于每个查询都具有固定的形状（例如已知列名），与数据无关，因此需要这样做。网上有许多示例，请搜索“动态透视”-例如https://dev59.com/YGkv5IYBdhLWcg3wtTC7 - user166390

同时，https://dev59.com/WmjWa4cB1Zd3GeqPq3dR?rq=1，https://dev59.com/oUvSa4cB1Zd3GeqPbRG6?rq=1等。 - user166390

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

这可以通过使用PIVOT函数来实现。GROUP BY可以工作，因为你有一个指示器使每一行都是不同的。对于你的数据，指示器是rowNumber列。

如果你有固定数量的列，那么你将需要使用静态旋转硬编码它们。代码将类似于以下内容：

select [1], [2], [3]
from
(
  select colNumber, RowNumber, CellData
  from yourtable
) src
pivot
(
  max(CellData)
  for colnumber in ([1], [2], [3])
) piv;

请查看带演示的SQL Fiddle。

在您的情况下，您提到您将有一个未知数量的列。如果是这种情况，那么您需要使用动态 SQL 来构建要枢轴的列列表。我演示了静态版本，因为它使得将代码转换为动态 SQL 更容易。

动态 SQL 版本的关键是获取列的列表，这是通过查询您的表并创建列名字符串来完成的。这是使用 FOR XML PATH 完成的：

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

然后将此列表添加到您生成的查询字符串中，最终代码如下：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
             from 
             (
                select colNumber, rowNumber, CellData
                from yourtable
            ) x
            pivot 
            (
                min(CellData)
                for colNumber in (' + @cols + ')
            ) p '

execute(@query)

查看 SQL Fiddle with Demo。

两者都给出结果：

|      1 |       2 |      3 |
-----------------------------
| Orange |   Apple | Banana |
|  Grape |    Corn |  Lemon |
| Tomato | Lettuce |  Onion |