ProAnswers.org

Merging Many Rows into single Text String?

Consider a table holding names, with three rows:

Simi
Khushi
Akshay
Is there an easy way to turn this into a single string of Simi, Khushi,Akshay?

I also found similar problem when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID StudentName


1 Simi
1 Khushi
1 Akshay

Result I expected was:

SubjectID StudentName


1 Simi, Khushi, Akshay

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]