»

Concatenating many rows into a single row using SQL

 

Hi,There have been many instances where we do need to concatenate multiple rows into a single row that too through SQL. Let us try to achieve this right here. Suppose we have a table by the name of Students. And its contents are shown below:

SubjectId StudentId
1 A
1 B
1 C
2 D
2 E

And the result that we want is something like this:

SubjectId StudentId
1 A,B,C
2 D,E

This can be achieved by the below query:

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]

If required we can further optimize the query:

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, 200) [Students]

About the author

saurabhr2

Leave a Reply

Your email address will not be published. Required fields are marked *