Home >Database >Mysql Tutorial >How to Merge Multiple SQL Rows into One with Dynamically Generated Columns Based on a Grouping Column?
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!