How to Concatenate Text from Multiple SQL Server Rows into a Single String?

SQL Server Multiline Text Connection: XML PATH Method

The need to combine multiple lines of text data into a single string is common. For example, there is a table containing names:

<code>Peter
Paul
Mary</code>

The goal is to convert this into a unified string: "Peter, Paul, Mary".

For SQL Server 2017 or Azure users, consider the solution provided by Mathieu Renda. However, for earlier versions (such as SQL 2005), the XML PATH method provides an efficient method for this connection task.

The following is an example of a table called STUDENTS:

SubjectID	StudentName
1	Mary
1	John
1	Sam
2	Alaina
2	Edward

Desired output is:

SubjectID	StudentName
1	Mary, John, Sam
2	Alaina, Edward

To achieve this, use the following T-SQL code:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT ST2.SubjectID,
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
        GROUP BY ST2.SubjectID
    ) [Main]

This query retrieves the SubjectID and concatenated StudentName values ​​and omits the trailing comma from the result string.

Alternatively, you can use a cleaner approach by concatenating the commas at the beginning and using STUFF to remove the first comma:

SELECT ST2.SubjectID, 
    STUFF(
        (
            SELECT ',' + ST1.StudentName AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 1, 1, '') [Students]
FROM dbo.Students ST2
GROUP BY ST2.SubjectID

The above is the detailed content of How to Concatenate Text from Multiple SQL Server Rows into a Single String?. For more information, please follow other related articles on the PHP Chinese website!