CODEWITHSUNDEEP
sqlsql-server
newlearner
newlearner

Reputation: 39

SQL Query resultset output as a single row using comma

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;

Upvotes: 0

Views: 381

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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;

Upvotes: 1

Toby
Toby

Reputation: 7

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]

Upvotes: 0

Related Questions