SQL で複数の列を持つ複数の行を動的にグループ化してマージする方法

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

表形式のデータを操作する場合、多くの場合、1 つ以上の列に基づいてレコードをグループ化し、その結果を複数の列を含む 1 つの行に結合する必要があります。これは、各グループに関連付けられた複数の値を扱う場合に特に便利です。

テスト結果を含むテーブルがあり、各行が特定のテスト タイプとそれに対応する結果を表すとします。たとえば、「結果」テーブルには次のスキーマが含まれる場合があります:

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)

これらの結果を「TestType」でグループ化し、結果ごとに複数の列を含む新しい行を作成するには、「クロスタブ」または「ピボット」操作を使用できます。 1 つの方法は、「ROW_NUMBER」関数を使用してグループ内の各結果にシーケンス番号を割り当て、条件付き集計を使用して必要な値を抽出することです。

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;

このクエリは次の出力を生成します:

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

ただし、この方法は結果の固定数に制限されます。任意の数の結果に対応できる動的なソリューションを作成するには、「相互適用計数テーブル」と呼ばれる手法を使用できます。

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 --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;

このクエリは、「カウント テーブル」を使用して、テーブル内の結果の最大数に基づいて SQL ステートメントを動的に生成する一連の整数を作成します。この手法を使用すると、任意の数の結果を 1 つの行に結合できます。

これらのメソッドを使用すると、表形式のデータを効果的にグループ化し、より構造化された意味のある形式に結合して、データの分析と視覚化を簡素化できます。

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