0

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 :)

2 Answers 2

3

Your code shows 3 tables, could you describe their structure?

Also, your code is using for xml on Chapter table, which, I assume, is the reference of the codes, not the codes that are actually used in chapter. So, may be better to use xml for path on CodeChapterLinks table.

Like:

select  distinct
       Code,  
       STUFF((SELECT ', ' + c.chapter
             FROM CodeChapterLinks  c
             where c.code = t.code
             ORDER BY c.chapter
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From CodeChapterLinks  t

And by the way, stuff is not the function that concatenates the results, for xml does.

2
  • Limited on characters to reply :( Either way, using the code you supplied and slightly amending it, I came out something that worked, but I don't know how to add it as a comment within here :D Thanks so much for your assistance.
    – Jonno
    Jan 18, 2017 at 12:34
  • @Jonno - You can click the Check symbol on irimias's response to mark it as the help you needed.
    – RLF
    Jan 18, 2017 at 14:43
1

Using irimias' reply, I managed to achieve what I was looking for with the following:

select distinct
          C.Code,  
          STUFF((SELECT ', ' + CHAP.[Key]
                 FROM CodeChapterLinks CCL
                 Join Chapters CHAP on CCL.ChapterId = CHAP.Id
                 where CCL.CodeId = C.Id
                 ORDER BY C.Code
                 FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C

This worked spot on.

Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.