Combine multiple combinations to one

Question

Post reply

Combine multiple combinations to one

RZ52

Mr or Mrs. 500

Points: 536
More actions
March 28, 2013 at 8:25 am

#291597

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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]

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 2

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