Wednesday, August 25, 2010

In SQL Server, How to combine multiple row result into one row

Suppose You have below data
Subjectid   Name
1               XYZ
1               ABC
1               PQR
2               RPA
2               PBA
3               TGB

Expected Result :
Subjectid  Name
1              XYZ, ABC, PQR
2              RPA, PBA
3              TGB

SQL Query:
Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.Name + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]

No comments: