Please help me this:
I want to group by the TestType column, but Result should be split into colunms if they have the same 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)
and would like to return data in the format below:
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# column should be dynamic because each TestType can have multiple results.
8
As I mention in the comments, what you need here is a PIVOT or Cross tab; I prefer the latter so what I am going to use.
The non-dynamic solution to this would be as follows:
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;
The problem, however, is that this “locks” you into 3 results, but you suggest there is an indeterminate number of results. Therefore you need a dynamic solution.
The below will work up to 100 results. if you do need more columns than than, then add more CROSS JOINs to N in the CTE Tally. This results is something like this (which is quite messy).
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;
On more recent versions of SQL Server, you can make use of GENERATE_SERIES (2022+) and STRING_AGG (2017+) (replacing the Tally and FOR XML PATH (and STUFF) sections respectively):
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10),
@MaxTally int;
DECLARE @Delimiter varchar(30) = N',' + @CRLF + N' ';
SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
FROM dbo.Result
GROUP BY WorkOrder,
TestType) R;
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
(SELECT STRING_AGG(CONCAT(N'MAX(CASE RN WHEN ',GS.value,N' THEN Result END) AS Result',GS.value),@Delimiter) WITHIN GROUP (ORDER BY GS.value)
FROM GENERATE_SERIES(1,@MaxTally,1) GS) + @CRLF +
N'FROM RNs R' + @CRLF +
N'GROUP BY WorkOrder,' + @CRLF +
N' TestType;';
0
You may try this and extend the logic.
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
1
Here’s a version of the solution that uses dynamic SQL to handle an unknown number of Result columns:
DECLARE @SQL nvarchar(MAX),
@MaxResults int;
-- Determine the maximum number of results for any TestType
SELECT @MaxResults = MAX(COUNT(*))
FROM dbo.Result
GROUP BY WorkOrder, TestType;
-- Generate the dynamic SQL statement
WITH Tally AS (
SELECT TOP (@MaxResults) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(X) -- 10 rows, adjust if needed
)
SELECT @SQL = 'WITH RNs AS (
SELECT WorkOrder, TestType, Result,
ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN
FROM dbo.Result
)
SELECT WorkOrder, TestType, ' +
STRING_AGG('MAX(CASE WHEN RN = ' + CONVERT(varchar, RN) + ' THEN Result END) AS Result' + CONVERT(varchar, RN), ', ') +
' FROM RNs
GROUP BY WorkOrder, TestType;'
FROM Tally;
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;