asked on Concatenate text from multiple rows into a single text string in SQL server
I need an SQL to produce the result as shown in EXPECTED RESULT
There are three table which must be combined to produce the result as "EXPECTED RESULT". I have been trying several SQL statements with COALESCE, XML PATH, etc... but to no avail.
Any help is greatly appreciated
Course table
============
ID1 Name1
21 Math
22 Chemistry
23 Physics
24 History
Student table
=============
Student ID2 Name2
1 Joe
2 Mary
3 Peter
Attendance table
================
ID1 ID2
21 2
21 3
22 1
22 2
22 3
23 3
24 2
24 3
Expected result
==============
21 Mary, Peter
22 Joe, Mary, Peter
23 Peter
24 Mary
24 Peter
There are three table which must be combined to produce the result as "EXPECTED RESULT". I have been trying several SQL statements with COALESCE, XML PATH, etc... but to no avail.
Any help is greatly appreciated
Course table
============
ID1 Name1
21 Math
22 Chemistry
23 Physics
24 History
Student table
=============
Student ID2 Name2
1 Joe
2 Mary
3 Peter
Attendance table
================
ID1 ID2
21 2
21 3
22 1
22 2
22 3
23 3
24 2
24 3
Expected result
==============
21 Mary, Peter
22 Joe, Mary, Peter
23 Peter
24 Mary
24 Peter
SQL
Last Comment
Errol Farro
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
In your expected results, why do you have 2 rows with ID 24?
Try this:
SELECT DISTINCT Course.ID1, STUFF((SELECT ',' + Name FROM Student s INNER JOIN Attendence a ON s.ID2 = a.ID2 WHERE a.ID1 = c.ID1 FOR XML PATH('')), 1, 1, '')
FROM Course c
Slight change:
SELECT DISTINCT c.ID1,
STUFF((SELECT ',' + Name2 FROM Student s INNER JOIN Attendance a ON s.ID2 = a.ID2 WHERE a.ID1 = c.ID1 FOR XML PATH('')), 1, 1, '')
FROM Course c
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Thank you gents