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

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

É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

Errol Farro

ASKER

Thank you gents