SQLで列をグループ化し、複数の行を複数の列を持つ単一の行に結合する方法は?

SQL Server で列をグループ化し、複数の行を複数の列を持つ 1 つの行に結合します

場合によっては、データを特定の列ごとにグループ化し、関連する行からの複数の値を複数の列を含む 1 つの行に集計したい場合があります。次の状況を例として考えてみましょう:

次の列を含む Result というテーブルがあります:

• WorkOrder: 特定のオーダーの識別子
• TestType: 実行されたテストのタイプ
• 結果: テスト結果

結果テーブルのデータは次のようになります:

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 の 3 つの列に制限します。ただし、結果の動的な量については、より複雑なソリューションが必要です。

動的ソリューション

不確実な数の結果を処理するには、必要な列を自動的に作成する動的 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;

このクエリは、TestType の結果の最大数に基づいて十分な数の Result 列を作成する動的 SQL ステートメントを生成します。 Tally と呼ばれる CTE (Common Table Expression) を使用して、Result 列の列番号を動的に生成します。

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