SQL Query resultset output as a single row using comma

Question

I have these 3 tables as below.

    Table: Student
        -------------
        |  ID | Name| 
        +-----+-----+
        | S01 |Alex |
        | S02 |Sam  |
        | S03 |May  |
        -------------

    Table: Book
        --------------
        |  ID | Name | 
        +-----+------+
        | B01 |BookA |
        | B02 |BookB |
        | B03 |BookC |
        --------------

    Table:StudentBooks
        --------------
        |  SID| BID  | 
        +-----+------+
        | S01 |B01   |
        | S02 |B02   |
        | S01 |B03   |
        | S02 |B03   |
        --------------

Here is the output I want to get.

    -----------------------
    |  Name |     Book    | 
    +-------+-------------+
    | Alex  |BookA, Book C|
    | Sam   |BookB, Book C|
    | May   |             |
    -----------------------

I tried with the following code, but it seems not able to get the correct book name based on the student name. My current output is that all the books are shown in every row, including student named 'May' which not allocated with any books.

SELECT s.Name AS Name,
       STUFF((SELECT ',' + b.Name
              FROM StudentBook sb
                   JOIN Student s ON s.ID = sb.SID
                   JOIN Book b ON b.ID = sb.BID
              WHERE s.Name = s.Name
             FOR XML PATH('')),1,1,'') AS Book
FROM StudentBooks sb
     JOIN Book b ON b.ID = sb.BID
     JOIN Student s ON s.ID = sb.SID;

`WHERE s.Name = s.Name`? When is the value of `s.Name` not going to be the value of `s.Name` (apart from when it's `NULL`)? — Thom A, Apr 08 '20 at 09:34

Yogesh Sharma · Accepted Answer · 2020-04-08T09:49:11.323

1

Your inner & outer references are not matching. You are referencing student name to book :

SELECT s.Name AS Name,
       STUFF((SELECT ',' + b.Name
              FROM StudentBook sb JOIN 
                   Book b 
                   ON b.ID = sb.BID
              WHERE sb.sid = s.id
              FOR XML PATH('')
              ), 1, 1, ''
             ) AS Book
FROM Student s;

edited Apr 08 '20 at 09:49

answered Apr 08 '20 at 09:36

Yogesh Sharma

49,870
5
26
52

I tried with this but I'm still getting all the books for every students.. Even student name 'May' who is not allocated with any books. – newlearner Apr 08 '20 at 09:54
@newlearner. . . This does what you want with `May` who doesn't have any books. – Yogesh Sharma Apr 08 '20 at 10:05
yes it works! sorry I missed out Where statement. Thank you so much!! – newlearner Apr 08 '20 at 10:09

score 0 · Answer 2 · answered Apr 08 '20 at 09:47

0

STRING_AGG will also solve the problem

SELECT  S.[Name],
    STRING_AGG(B.[Name], ', ') AS Books
FROM StudentBooks AS SB WITH(NOLOCK)
    INNER JOIN Student AS S WITH(NOLOCK) ON S.ID = SB.SID
    INNER JOIN Book AS B WITH(NOLOCK) ON B.ID = SB.BID
GROUP BY S.[Name]

answered Apr 08 '20 at 09:47

Toby

7
1

its having error :'STRING_AGG' is not a recognized built-in function name. – newlearner Apr 08 '20 at 09:52
I saw "sql-server" was tagged, is this not what you are using? – Toby Apr 08 '20 at 09:56
https://stackoverflow.com/questions/40923080/string-agg-is-not-a-recognized-built-in-function-name – Toby Apr 08 '20 at 10:03

SQL Query resultset output as a single row using comma

2 Answers2