Home >Database >Mysql Tutorial >How to Merge Multiple SQL Rows into One with Dynamically Generated Columns Based on a Grouping Column?

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

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2025-01-22 01:22:09410browse

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:

<code class="language-sql">WITH RNs AS (
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN
    FROM dbo.Result
)</code>

Build a dynamic SQL statement:

<code class="language-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;'</code>

Execute dynamic SQL statement:

<code class="language-sql">PRINT @SQL; -- 查看SQL语句

EXEC sys.sp_executesql @SQL;</code>

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn