一列一组，多行为一排多列
一列一组，多行为一排多列
提问于 2020-08-21 10:31:44
请帮我这个忙：
我希望按列TestType进行分组，但如果结果具有相同的TestType，则应将其拆分为冒号。
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 NULLResult#列应该是动态的，因为每个TestType都有许多结果。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-08-21 11:23:25
正如我在注释中提到的，这里需要的是一个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 JOIN更多的列，那么在CTE Tally中向N添加更多的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;Stack Overflow用户
发布于 2020-08-21 11:47:55
您可以尝试这样做并扩展逻辑。
select *,((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY Testtype) Id from Result) a where a.Id='1' and a.TestType=b.TestType )) Result1,
((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY Testtype) Id from Result) a where a.Id='2' and a.TestType=b.TestType )) Result2,
((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY Testtype) Id from Result) a where a.Id='3' and a.TestType=b.TestType )) Result3
from (
select Max(WorkOrder) WorkOrder,TestType from Result group by TestType ) b页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63521138
复制相关文章
相似问题
腾讯云开发者
