SQL で列ごとにグループ化し、複数の行を動的な列数の 1 つに結合する方法

SQL Server の動的列グループ化と複数行のマージ

質問:

データベース テーブルには、TestType と WorkOrder の組み合わせごとに複数の結果行があります。目標は、TestType ごとにグループ化し、結果を 1 つの行に結合し、それぞれの Result 値を別の列に入れることです。

解決策:

不確実な数の結果を処理するには、動的ソリューションが必要です。次のスクリプトは、最大 100 件の結果を処理できます。結果が 100 件を超える場合は、CTE Tally でさらに CROSS JOIN を追加する必要があります。

<code class="language-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) --100 行，更多行需要添加更多 N
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 --ORDER BY 应为您的 ID/始终递增列' + @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;</code>

サンプルデータ:

<code class="language-sql">CREATE TABLE Result(WorkOrder varchar(10), TestType varchar(20), Result decimal(10,2));
INSERT INTO Result (WorkOrder, TestType, Result) VALUES 
('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)</code>

期待される出力:

<code>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</code>

以上がSQL で列ごとにグループ化し、複数の行を動的な列数の 1 つに結合する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。