SQL Server 动态列分组及多行合并
问题：
数据库表中，每种 TestType 和 WorkOrder 组合有多行结果。目标是按 TestType 分组，并将结果合并到单行中，每个 Result 值位于单独一列。
解决方案：
需要动态解决方案来处理不确定数量的结果。以下脚本最多可处理 100 个结果。超过 100 个结果，需要在 CTE Tally 中添加更多 CROSS JOINs。
<code class="language-sql">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) --100 行，更多行需要添加更多 N
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;</code>示例数据：
<code class="language-sql">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)</code>预期输出：
<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>
以上是如何在 SQL 中按列分组并将多行合并为具有动态列数的行？的详细内容。更多信息请关注PHP中文网其他相关文章！
