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
Errol FarroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
this is working for me:
--DECLARE @Course TABLE(ID1 INT, Name1 VARCHAR(50))

--INSERT INTO @Course ( ID1, Name1 )
--VALUES (21, 'Math' )
--, (22, 'Chemistry' )
--, (23, 'Physics' )
--, (24, 'History' )

DECLARE @Student TABLE(ID2 INT, Name2 VARCHAR(50))

INSERT INTO @Student ( ID2, Name2 )
VALUES (1, 'Joe' )
, (2, 'Mary' )
, (3, 'Peter' )

DECLARE @Attendance TABLE ( ID1 INT, ID2 INT )

INSERT INTO @Attendance ( ID1, ID2 )
VALUES (21, 2)
, (21,3)
, (22,1)
, (22,2)
, (22,3)
, (23,3)
, (24,2)
, (24,3)

--SELECT * FROM @Course
SELECT * FROM @Student
SELECT * FROM @Attendance

; WITH cteData AS (
SELECT A.ID1, A.ID2, S.Name2 
FROM @Attendance AS A
--INNER JOIN @Course AS C
--ON C.ID1 = A.ID1
INNER JOIN @Student AS S
ON S.ID2 = A.ID2
)

SELECT DISTINCT A.ID1 
, STUFF(
       (SELECT ', ' + B.Name2
        FROM cteData AS B
        WHERE B.ID1 = A.ID1
        ORDER BY B.Name2
        FOR XML PATH('')
       )
       , 1
       , 2
       , '') AS ExpectedResults
FROM cteData AS A
ORDER BY A.ID1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
In your expected results, why do you have 2 rows with ID 24?
Doug BishopDatabase DeveloperCommented:
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

Doug BishopDatabase DeveloperCommented:
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 FarroAuthor Commented:
Thank you gents
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.