按列和多行分组为一行多列

佑星

请帮我这个：
我想按列TestType分组，但如果它们具有相同的TestType，则应将Result拆分为colunms
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)
我希望SQL以这种格式返回数据
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
Result＃列应该是动态的，因为每个TestType都有很多结果

解决方案:


            正如我在评论中所提到的，这里您需要的是PIVOT或交叉标签；我更喜欢后者，所以我将要使用的东西。
对此的非动态解决方案如下：
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;
但是，问题在于这会将您“锁定”到3个结果中，但是您建议存在不确定数量的结果。因此，您需要一个动态的解决方案。
以下将最多处理100个结果。如果你需要更多的列比超过，再加入更多的CROSS JOINs到N的CTE
Tally。结果是这样的（非常混乱）。
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;

按列和多行分组为一行多列

佑星 LV1