concat string many row to single row

SubjectID       StudentName
----------      -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID       StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL:
Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.StudentName + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]
เอาค่าหลายๆ record มาต่อกันเป็น record เดียว

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s