如何在 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中文網其他相關文章！