You had a good idea, but you missed the creation of the comma separated values. To learn how to do it you can read the following article. Creating a comma-separated list (SQL Spackle)[/url]

Here's a code working with your sample data. Check how I included it in a way that anyone can copy it and execute it directly in SSMS without any modification. I strongly suggest that you do this on your next posts to get better and faster help.

CREATE TABLE #Case( CaseID int, Name char(3))

INSERT #Case VALUES

(12341, 'XYZ'),

(23451, 'ZZZ'),

(90892, 'XXQ')

CREATE TABLE #CaseCode( CaseID int, CaseCode char(3), TypeFlag char(1))

INSERT #CaseCode VALUES

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

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

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

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

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

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,

Name,

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

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

FROM CTE

GROUP BY CaseID,

Name

DROP TABLE #CaseCode

DROP TABLE #Case