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
ORDER BY ST1.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
เอาค่าหลายๆ record มาต่อกันเป็น record เดียว
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: