I am wanting to see a list of codes with their relevant chapters in my database. A code can have more than one chapter assigned, therefore meaning more than one row in the database.
This is how the database looks:
Code | Chapter
000 | 1.1.0
000 | 3.0.0
123 | 1.1.0
123 | 1.2.0
456 | 1.7.0
789 | 1.2.0
789 | 2.1.4
789 | 5.1.7
I would like to do a STUFF function to display the Code and the Chapter/s on one line, but for every Code in the system. This is how I would like it to look:
Code | Chapter
000 | 1.1.0, 3.0.0
123 | 1.1.0, 1.2.0
456 | 1.7.0
789 | 1.2.0, 2.1.4, 5.1.7
But I am receiving the following:
Code | Chapter
000 | 1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
123 | 1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
456 | 1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
789 | 1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
The SQL I am using is:
select distinct
C.Code,
STUFF((SELECT ', ' + CHAP.[Key]
FROM Chapters CHAP
ORDER BY CHAP.[Key]
FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C
left outer join CodeChapterLinks CCL
ON C.Id = CCL.CodeId
left outer join Chapters CHAP
ON CCL.ChapterId = CHAP.Id
ORDER BY C.Code asc
Any assistance is much appreciated :)