Concatenate text from multiple rows into a single text string in SQL server

Errol Farro
Errol Farro used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Senior .Net Consultant
Top Expert 2016
Commented:
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

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
In your expected results, why do you have 2 rows with ID 24?
Doug BishopDatabase Developer

Commented:
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 Developer

Commented:
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

Author

Commented:
Thank you gents

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial