How to Merge Multiple SQL Rows into One with Dynamically Generated Columns Based on a Grouping Column?

Dynamically generate columns based on grouping columns and merge multiple rows of SQL data

Question:

How to group data by a specific column and merge multiple rows with the same column value into a single row, generating multiple columns at the same time? Specifically, you need to group by the "TestType" column and split the "Result" column into a separate column for each matching "TestType" value.

Answer:

This can be achieved using the Crosstab (PIVOT) in SQL Server. However, if the number of columns per group is dynamic (i.e. may change), a more flexible solution is required:

Dynamic solution using FOR XML PATH:

Create CTE with line number:

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

Build a dynamic SQL statement:

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

Execute dynamic SQL statement:

PRINT @SQL; -- 查看SQL语句

EXEC sys.sp_executesql @SQL;

This solution will dynamically generate columns based on the maximum number of results for each "TestType", resulting in the desired output. @CRLF variables have been added to the code for better code readability and enhanced cross-platform compatibility.

The above is the detailed content of How to Merge Multiple SQL Rows into One with Dynamically Generated Columns Based on a Grouping Column?. For more information, please follow other related articles on the PHP Chinese website!