Cacher is the code snippet organizer for pro developers

select distinct ST2.SubjectID, 
    substring(
        (
            select ',' + ST1.StudentName as [text()]
            from dbo.Students ST1
            where ST1.SubjectID = ST2.SubjectID
            order by ST1.SubjectID
            for xml path ('')
        ), 2, 1000) [Students]
from dbo.Students ST2