I was kind of curious how Luis's method would compare to something like this (using subqueries):

SELECT CaseID, Name

,PrimaryCodes=STUFF((

SELECT ',' + CaseCode

FROM #CaseCode b

WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'

ORDER BY CaseCode

FOR XML PATH(''), TYPE).value('.','varchar(max)'

), 1, 1, '')

,SecondaryCodes=STUFF((

SELECT ',' + CaseCode

FROM #CaseCode b

WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'

ORDER BY CaseCode

FOR XML PATH(''), TYPE).value('.','varchar(max)'

), 1, 1, '')

FROM #Case a

So I put it into a test harness:

CREATE TABLE #Case( CaseID int, Name varchar(8));

INSERT INTO #Case VALUES

(12341, 'XYZ'),

(23451, 'ZZZ'),

(90892, 'XXQ');

WITH Tally (n) AS (

SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #Case

SELECT 100000+n, 'A' + CAST(ABS(CHECKSUM(NEWID()))%1000000 AS VARCHAR(7))

FROM Tally

CREATE TABLE #CaseCode( CaseID int, CaseCode varchar(8), TypeFlag char(1));

INSERT INTO #CaseCode VALUES

(12341, '001', 'P'),

(12341, '003', 'S'),

(90892, '111', 'S'),

(90892, '222', 'S'),

(90892, '333', 'P'),

(90892, '999', 'P');

WITH Tally (n) AS (

SELECT TOP 1200000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #CaseCode

SELECT 100000+n, 'A' + CAST(ABS(CHECKSUM(NEWID()))%1000000 AS VARCHAR(7))

,CASE WHEN ABS(CHECKSUM(NEWID()))%1000000 > 750000 THEN 'S' ELSE 'P' END

FROM Tally;

DECLARE @CaseID INT, @Name VARCHAR(8), @PrimaryCodes VARCHAR(8000)

,@SecondaryCodes VARCHAR(8000)

PRINT 'LUIS';

SET STATISTICS TIME ON;

WITH CTE AS(

SELECT c.CaseID,

c.Name,

cc.TypeFlag,

Codes = STUFF((

SELECT ',' + CaseCode

FROM #CaseCode x

WHERE x.CaseID = cc.CaseID

AND x.TypeFlag = cc.TypeFlag

ORDER BY CaseCode

FOR XML PATH(''),

TYPE).value('.','varchar(max)'),1,1,'')

FROM #CaseCode cc

RIGHT

JOIN #Case c ON cc.CaseID = c.CaseID

)

SELECT @CaseID=CaseID,

@Name=Name

,@PrimaryCodes=MAX( CASE WHEN TypeFlag = 'P' THEN Codes END) --AS PrimaryCodes,

,@SecondaryCodes=MAX( CASE WHEN TypeFlag = 'S' THEN Codes END) --AS SecondaryCodes

FROM CTE

GROUP BY CaseID,

Name

SET STATISTICS TIME OFF;

PRINT 'DWAIN';

SET STATISTICS TIME ON;

SELECT @CaseID=CaseID, @Name=Name

,@PrimaryCodes=STUFF((

SELECT ',' + CaseCode

FROM #CaseCode b

WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'

ORDER BY CaseCode

FOR XML PATH(''), TYPE).value('.','varchar(max)'

), 1, 1, '')

,@SecondaryCodes=STUFF((

SELECT ',' + CaseCode

FROM #CaseCode b

WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'

ORDER BY CaseCode

FOR XML PATH(''), TYPE).value('.','varchar(max)'

), 1, 1, '')

FROM #Case a

SET STATISTICS TIME OFF

DROP TABLE #CaseCode

DROP TABLE #Case

And I got these timing results:

LUIS

SQL Server Execution Times:

CPU time = 65547 ms, elapsed time = 65826 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.

DWAIN

SQL Server Execution Times:

CPU time = 61922 ms, elapsed time = 62450 ms.

Looks pretty close so I'd guess actual times would depend on your real data.