March 28, 2013 at 8:25 am
Hi,
The following test condition :
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment ([Sample_ID] INT, [Rep_ID] INT, [Result] VARCHAR(20))
INSERT INTO #testEnvironment
SELECT 1, 1, 'O152' UNION ALL SELECT 1, 2, 'O2' UNION ALL
SELECT 1, 3, 'O157' UNION ALL SELECT 1, 4, 'O154' UNION ALL
SELECT 2, 1, 'O5' UNION ALL SELECT 2, 2, 'Negative' UNION ALL
SELECT 2, 3, 'O1' UNION ALL SELECT 3, 1, 'O157' UNION ALL
SELECT 3, 2, 'O1' UNION ALL SELECT 3, 3, 'O1' UNION ALL
SELECT 4, 1, 'O157' UNION ALL SELECT 4, 2, 'Negative' UNION ALL
SELECT 4, 3, 'O2' UNION ALL SELECT 4, 4, 'O152' UNION ALL SELECT 5, 1, 'Negative' UNION ALL
SELECT 5, 2, 'Negative' UNION ALL SELECT 5, 3, 'Negative' UNION ALL
SELECT 6, 1, 'O154' UNION ALL SELECT 6, 2, 'O157' UNION ALL SELECT 6, 3, 'O152' UNION ALL
SELECT 7, 1, 'Negative' UNION ALL SELECT 7, 2, 'O152' UNION ALL SELECT 7, 3, 'O157'
;WITH SampleData AS (
SELECT [Sample_ID], ISNULL([Interim],[Profile]) AS [Profile]
FROM (SELECT [Sample_ID],
CAST(CASE WHEN MAX([Result]) = MIN([Result]) AND MAX([Result]) = 'Negative'
THEN 'Negative'
ELSE NULL END AS NVARCHAR(MAX))
FROM #testEnvironment
GROUP BY [Sample_ID]
)a([Sample_ID],[Interim])
CROSS APPLY (SELECT ISNULL(STUFF((SELECT ';'+[Result]
FROM #testEnvironment
WHERE a.Sample_ID = Sample_ID
AND CHARINDEX('O15',[Result]) > 0
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
,'Non-pathogen')
)b([Profile])
)
SELECT [Profile], COUNT([Profile]) AS [Count]
FROM SampleData
GROUP BY [Profile]
Returns :
Profile Count
-------------------- -------
Negative 1
Non-pathogen 1
O152;O157 1
O152;O157;O154 1
O154;O157;O152 1
O157 1
O157;O152 1
As you see different variations of same combination are counted separately. How can I avoid it and have :
Profile Count
-------------------- -------
Negative 1
Non-pathogen 1
O152;O157 2
O152;O157;O154 2
O157 1
Thanks in advance for any suggestion.
March 28, 2013 at 8:43 am
Add on ORDER BY where you concatenate your values:
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment ([Sample_ID] INT, [Rep_ID] INT, [Result] VARCHAR(20))
INSERT INTO #testEnvironment
SELECT 1, 1, 'O152' UNION ALL SELECT 1, 2, 'O2' UNION ALL
SELECT 1, 3, 'O157' UNION ALL SELECT 1, 4, 'O154' UNION ALL
SELECT 2, 1, 'O5' UNION ALL SELECT 2, 2, 'Negative' UNION ALL
SELECT 2, 3, 'O1' UNION ALL SELECT 3, 1, 'O157' UNION ALL
SELECT 3, 2, 'O1' UNION ALL SELECT 3, 3, 'O1' UNION ALL
SELECT 4, 1, 'O157' UNION ALL SELECT 4, 2, 'Negative' UNION ALL
SELECT 4, 3, 'O2' UNION ALL SELECT 4, 4, 'O152' UNION ALL SELECT 5, 1, 'Negative' UNION ALL
SELECT 5, 2, 'Negative' UNION ALL SELECT 5, 3, 'Negative' UNION ALL
SELECT 6, 1, 'O154' UNION ALL SELECT 6, 2, 'O157' UNION ALL SELECT 6, 3, 'O152' UNION ALL
SELECT 7, 1, 'Negative' UNION ALL SELECT 7, 2, 'O152' UNION ALL SELECT 7, 3, 'O157'
;WITH SampleData AS (
SELECT [Sample_ID], ISNULL([Interim],[Profile]) AS [Profile]
FROM (SELECT [Sample_ID],
CAST(CASE WHEN MAX([Result]) = MIN([Result]) AND MAX([Result]) = 'Negative'
THEN 'Negative'
ELSE NULL END AS NVARCHAR(MAX))
FROM #testEnvironment
GROUP BY [Sample_ID]
)a([Sample_ID],[Interim])
CROSS APPLY (SELECT ISNULL(STUFF((SELECT ';'+[Result]
FROM #testEnvironment
WHERE a.Sample_ID = Sample_ID
AND CHARINDEX('O15',[Result]) > 0
ORDER BY Result -- << added this
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
,'Non-pathogen')
)b([Profile])
)
SELECT [Profile], COUNT([Profile]) AS [Count]
FROM SampleData
GROUP BY [Profile]
March 28, 2013 at 8:49 am
Lynn Pettis (3/28/2013)
Add on ORDER BY where you concatenate your values:
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment ([Sample_ID] INT, [Rep_ID] INT, [Result] VARCHAR(20))
INSERT INTO #testEnvironment
SELECT 1, 1, 'O152' UNION ALL SELECT 1, 2, 'O2' UNION ALL
SELECT 1, 3, 'O157' UNION ALL SELECT 1, 4, 'O154' UNION ALL
SELECT 2, 1, 'O5' UNION ALL SELECT 2, 2, 'Negative' UNION ALL
SELECT 2, 3, 'O1' UNION ALL SELECT 3, 1, 'O157' UNION ALL
SELECT 3, 2, 'O1' UNION ALL SELECT 3, 3, 'O1' UNION ALL
SELECT 4, 1, 'O157' UNION ALL SELECT 4, 2, 'Negative' UNION ALL
SELECT 4, 3, 'O2' UNION ALL SELECT 4, 4, 'O152' UNION ALL SELECT 5, 1, 'Negative' UNION ALL
SELECT 5, 2, 'Negative' UNION ALL SELECT 5, 3, 'Negative' UNION ALL
SELECT 6, 1, 'O154' UNION ALL SELECT 6, 2, 'O157' UNION ALL SELECT 6, 3, 'O152' UNION ALL
SELECT 7, 1, 'Negative' UNION ALL SELECT 7, 2, 'O152' UNION ALL SELECT 7, 3, 'O157'
;WITH SampleData AS (
SELECT [Sample_ID], ISNULL([Interim],[Profile]) AS [Profile]
FROM (SELECT [Sample_ID],
CAST(CASE WHEN MAX([Result]) = MIN([Result]) AND MAX([Result]) = 'Negative'
THEN 'Negative'
ELSE NULL END AS NVARCHAR(MAX))
FROM #testEnvironment
GROUP BY [Sample_ID]
)a([Sample_ID],[Interim])
CROSS APPLY (SELECT ISNULL(STUFF((SELECT ';'+[Result]
FROM #testEnvironment
WHERE a.Sample_ID = Sample_ID
AND CHARINDEX('O15',[Result]) > 0
ORDER BY Result -- << added this
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')
,'Non-pathogen')
)b([Profile])
)
SELECT [Profile], COUNT([Profile]) AS [Count]
FROM SampleData
GROUP BY [Profile]
Dear Lynn,
Thanks a lot. It crossed my mind too that I need an "ORDER BY" somewhere but I couldn't find where.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy