Avatar of Errol Farro
Errol Farro
Flag for Aruba 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
SQL

Avatar of undefined
Last Comment
Errol Farro

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Éric Moreau

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
Sign up - Free for 7 days
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
Éric Moreau

In your expected results, why do you have 2 rows with ID 24?
D B

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

Open in new window

D B

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

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Errol Farro

ASKER
Thank you gents