如何在 SQL Server 中将多行文本连接成单个文本字符串 [英] How to concatenate text from multiple rows into a single text string in SQL Server

问题描述

考虑一个包含名称的数据库表，三行:

Consider a database table holding names, with three rows:

Peter
Paul
Mary

有没有一种简单的方法可以将其转换为 Peter, Paul, Mary 的单个字符串?

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

推荐答案

如果您使用的是 SQL Server 2017 或 Azure，请参阅 Mathieu Renda回答.

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

当我尝试连接具有一对多关系的两个表时，我遇到了类似的问题.在 SQL 2005 中，我发现 XML PATH 方法可以非常轻松地处理行的连接.

I had a similar issue 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.

如果有一个表叫STUDENTS

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

我预期的结果是:

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

我使用了以下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 (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

如果您可以在开头连接逗号并使用 substring 跳过第一个，那么您可以以更紧凑的方式执行相同的操作，这样您就不需要执行子查询:

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-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 (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

这篇关于如何在 SQL Server 中将多行文本连接成单个文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！