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
– Thom A ♦, Commented Apr 8, 2020 at 9:34

Yogesh Sharma · Accepted Answer · 2020-04-08 09:49:11Z

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 8, 2020 at 9:49

answered Apr 8, 2020 at 9:36

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

newlearner Over a year ago

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.

Yogesh Sharma Over a year ago

@newlearner. . . This does what you want with May who doesn't have any books.

newlearner Over a year ago

yes it works! sorry I missed out Where statement. Thank you so much!!

Toby · Accepted Answer · 2020-04-08 09:47:32Z

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 8, 2020 at 9:47

Toby

71 bronze badge

3 Comments

newlearner Over a year ago

its having error :'STRING_AGG' is not a recognized built-in function name.

Toby Over a year ago

I saw "sql-server" was tagged, is this not what you are using?

Toby Over a year ago

stackoverflow.com/questions/40923080/…

Collectives™ on Stack Overflow

SQL Query resultset output as a single row using comma

2 Answers 2

3 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Linked

Related