# 按列和多行分组为一行多列 [英] Group by column and multiple Rows into One Row multiple columns

### 问题描述

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)

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

### 推荐答案

WITH RNs AS(
SELECT WorkOrder,
TestType,
Result,
ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
FROM dbo.Result)
SELECT WorkOrder,
TestType,
MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
TestType;

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 rows, add more Ns for more rows
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 should be your ID/always ascending column' + @CRLF +
N'    FROM dbo.Result)' + @CRLF +
N'SELECT WorkOrder,' + @CRLF +
N'       TestType,' + @CRLF +
--Using FOR XML PATH due to not knowing SQL Server version
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; --Your best friend.

EXEC sys.sp_executesql @SQL;