sql group by multiple row

Question

i have a table like this:

Table: Subject_Selection

Subject   Semester   Attendee
---------------------------------
ITB001    1          John
ITB001    1          Bob
ITB001    1          Mickey
ITB001    2          Jenny
ITB001    2          James
MKB114    1          John
MKB114    1          Erica

how could i have a query that result to this:

Subject   Semester   Attendee
---------------------------------
ITB001    1          John,Bob,Mickey    
ITB001    2          Jenny,James   
MKB114    1          John,Erica

M.Ali · Accepted Answer · 2014-02-02T20:53:51.103

Test Data

DECLARE @TABLE TABLE ([Subject] VARCHAR(20),Semester INT,Attendee VARCHAR(20))
INSERT INTO @TABLE VALUES
('ITB001',1,'John'),('ITB001',1,'Bob'),('ITB001',1,'Mickey'),('ITB001',2,'Jenny')
,('ITB001',2,'James'),('MKB114',1,'John'),('MKB114',1,'Erica')

Query

SELECT   [Subject]  
       , Semester        
       , STUFF((SELECT ', ' + Attendee [text()]
               FROM @TABLE
               WHERE Semester = T.Semester
               AND [Subject] = T.[Subject]
               FOR XML PATH('')), 1, 2, '') AS Attendee
FROM @TABLE T
GROUP BY [Subject], Semester

Subject is a reserve word in sql server use [] square brackets around it

Result Set

╔═════════╦══════════╦═══════════════════╗
║ Subject ║ Semester ║     Attendee      ║
╠═════════╬══════════╬═══════════════════╣
║ ITB001  ║        1 ║ John, Bob, Mickey ║
║ ITB001  ║        2 ║ Jenny, James      ║
║ MKB114  ║        1 ║ John, Erica       ║
╚═════════╩══════════╩═══════════════════╝

sql group by multiple row

1 Answers1