グループ化列に基づいて動的に生成された列を使用して複数の SQL 行を 1 つにマージする方法

列のグループ化に基づいて列を動的に生成し、SQL データの複数行をマージします

質問:

特定の列でデータをグループ化し、同じ列値を持つ複数の行を 1 つの行にマージして、複数の列を同時に生成するにはどうすればよいですか?具体的には、「TestType」列でグループ化し、一致する「TestType」値ごとに「Result」列を別の列に分割する必要があります。

答え:

これは、SQL Server の クロス集計 (PIVOT) を使用して実現できます。ただし、グループあたりの列の数が動的である (つまり、変化する可能性がある) 場合は、より柔軟なソリューションが必要です。

FOR XML PATH を使用した動的ソリューション:

行番号を使用して CTE を作成します:

WITH RNs AS (
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN
    FROM dbo.Result
)

動的 SQL ステートメントを構築します:

DECLARE @SQL nvarchar(MAX);
DECLARE @CRLF nvarchar(2) = CHAR(13) + CHAR(10); --回车换行符

-- 获取任何“TestType”的最大结果数
SELECT @MaxTally = MAX(C)
FROM (
    SELECT COUNT(*) AS C
    FROM dbo.Result
    GROUP BY WorkOrder,
             TestType
);

-- 创建具有最大行数的计数表
WITH Tally AS (
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N),
         (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)
)

-- 构建动态SQL语句
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 +
              -- 使用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;'

動的 SQL ステートメントを実行します:

PRINT @SQL; -- 查看SQL语句

EXEC sys.sp_executesql @SQL;

このソリューションは、各「TestType」の結果の最大数に基づいて列を動的に生成し、目的の出力をもたらします。 コードの読みやすさを向上させ、クロスプラットフォームの互換性を強化するために、@CRLF 変数がコードに追加されました。

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