如何在 SQL 中对列进行分组并将多行合并为单行多列？

SQL Server 中分组列并将多行合并为具有多列的单行

在某些情况下，您可能希望按特定列对数据进行分组，并将相关行中的多个值聚合到具有多列的单行中。让我们以以下情况为例：

您有一个名为 Result 的表，其中包含以下列：

• WorkOrder：特定订单的标识符
• TestType：执行的测试类型
• Result：测试结果

Result 表中的数据如下所示：

WorkOrder	TestType	Result
HP19002316	VitaminA	10.3
HP19002316	VitaminA	11.3
HP19002316	VitaminA	12.3
HP19002316	VitaminB	13.4
HP19002316	VitaminB	14.4
HP19002316	VitaminC	15.5
HP19002316	VitaminD	17.0

您希望将数据重新格式化为以下结构：

WorkOrder	TestType	Result1	Result2	Result3
HP19002316	VitaminA	10.3	11.3	12.3
HP19002316	VitaminB	13.4	14.4	NULL
HP19002316	VitaminC	15.5	NULL	NULL
HP19002316	VitaminD	17.0	NULL	NULL

这里的挑战是按 TestType 分组结果，并将多个 Result 值合并到单独的列中，标记为 Result1、Result2 等。

非动态解决方案

对于固定数量的结果，您可以使用一种直接的方法：

WITH RNs AS (
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN
    FROM dbo.Result
)
SELECT WorkOrder,
       TestType,
       MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
       MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
       MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
         TestType;

此查询将结果限制为三列，Result1、Result2 和 Result3。但是，对于动态数量的结果，需要更复杂的解决方案。

动态解决方案

为了处理不确定数量的结果，您可以使用一个动态 SQL 查询，该查询会自动创建必要的列：

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM dbo.Result
      GROUP BY WorkOrder,
               TestType) R;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2)
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
              N'    SELECT WorkOrder,' + @CRLF +
              N'           TestType,' + @CRLF +
              N'           Result,' + @CRLF +
              N'           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN' + @CRLF +
              N'    FROM dbo.Result)' + @CRLF +
              N'SELECT WorkOrder,' + @CRLF +
              N'       TestType,' + @CRLF +
              --由于不知道 SQL Server 版本，因此使用 FOR XML PATH
              STUFF((SELECT N',' + @CRLF +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
              N'FROM RNs R' + @CRLF +
              N'GROUP BY WorkOrder,' + @CRLF +
              N'         TestType;';

PRINT @SQL; --您的好朋友。

EXEC sys.sp_executesql @SQL;

此查询生成一个动态 SQL 语句，该语句根据任何 TestType 的最大结果数创建足够数量的 Result 列。它使用名为 Tally 的 CTE（公共表表达式）来动态生成 Result 列的列号。

以上是如何在 SQL 中对列进行分组并将多行合并为单行多列？的详细内容。更多信息请关注PHP中文网其他相关文章！