如何在 SQL Server 中将多行文本连接成单个文本字符串

拼搏现实的明天。 2024-03-30 16:24 27阅读 0赞

## 问： ##

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

Peter
    Paul
    Mary

有没有一种简单的方法可以把它变成一个 Peter, Paul, Mary 字符串？

## 答1: ##

> huntsbot.com – 高效赚钱，自由工作

如果您使用的是 SQL Server 2017 或 Azure，请参阅 Mathieu Renda answer。

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

如果有一个名为 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 跳过第一个逗号，那么您可以以更紧凑的方式执行相同的操作，这样您就不需要执行子查询：

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

`很好的解决方案。如果您需要处理 HTML 中的特殊字符，以下内容可能会有所帮助：Rob Farley: Handling special characters with FOR XML PATH('')。`

`如果名称包含 XML 字符（例如 < 或 &），显然这不起作用。请参阅@BenHinman 的评论。`

`注意：此方法依赖于 FOR XML PATH ('') 的未记录行为。这意味着它不应该被认为是可靠的，因为任何补丁或更新都可能改变它的功能。它基本上依赖于已弃用的功能。`

`@Welkaholism 底线是 FOR XML 旨在生成 XML，而不是连接任意字符串。这就是为什么它将 &、< 和 > 转义为 XML 实体代码（&、<、>）。我假设它也会在属性中将 " 和 ' 转义为 " 和 '。 不是 GROUP_CONCAT()、string_agg()、array_agg()、listagg() 等，即使您可以让它这样做。我们应该花时间要求微软实现适当的功能。`

`好消息：MS SQL Server will be adding string_agg in v.Next.，所有这些都可以消失。`

## 答2: ##

> HuntsBot周刊–不定时分享成功产品案例，学习他们如何成功建立自己的副业–huntsbot.com

此答案可能会返回意外结果为了获得一致的结果，请使用其他答案中详述的 FOR XML PATH 方法之一。

使用 COALESCE：

DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + Name 
    FROM People

只是一些解释（因为这个答案似乎得到了相对常规的观点）：

Coalesce 实际上只是一个有用的作弊工具，它完成了两件事：

1.  无需用空字符串值初始化 @Names。
2.  无需在最后剥离额外的分隔符。

如果一行有一个 NULL Name 值，上面的解决方案将给出不正确的结果（如果有一个 NULL，NULL 将使 @Names 在该行之后为 NULL，并且下一行将再次作为空字符串重新开始。用一个轻松修复两种解决方案：

DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + Name
    FROM People
    WHERE Name IS NOT NULL

或者：

DECLARE @Names VARCHAR(8000) 
    SELECT @Names = COALESCE(@Names + ', ', '') + 
        ISNULL(Name, 'N/A')
    FROM People

根据您想要的行为（第一个选项只是将 NULL 过滤掉，第二个选项使用标记消息将它们保留在列表中 \[用适合您的任何内容替换 ‘N/A’\]）。

`需要明确的是，coalesce 与创建列表无关，它只是确保不包含 NULL 值。`

`@Graeme Perrow它不排除NULL值（需要一个WHERE - 如果输入值之一是NULL，这将丢失结果），并且在这种方法中是必需的，因为：NULL + non-NULL -> NULL和非 NULL + NULL -> NULL； @Name 默认情况下也为 NULL，实际上，该属性在此处用作隐式标记来确定是否应添加 ', '。`

`请注意，这种连接方法依赖于 SQL Server 使用特定计划执行查询。我被这个方法发现了（加上一个 ORDER BY）。当它处理少量的行时，它工作得很好，但是对于更多的数据，SQL Server 选择了一个不同的计划，这导致选择第一个项目而没有任何连接。参见 Anith Sen 的this article。`

`此方法不能用作选择列表或 where 子句中的子查询，因为它使用 tSQL 变量。在这种情况下，您可以使用@Ritesh 提供的方法`

`这不是一种可靠的连接方法。它不受支持且不应使用（根据 Microsoft，例如 support.microsoft.com/en-us/kb/287515、connect.microsoft.com/SQLServer/Feedback/Details/704389）。它可以毫无预警地改变。使用在 stackoverflow.com/questions/5031204/… 中讨论的 XML PATH 技术我在这里写了更多：marc.durdin.net/2015/07/…`

## 答3: ##

> huntsbot.com洞察每一个产品背后的需求与收益，从而捕获灵感

SQL Server 2017+ 和 SQL Azure：STRING\_AGG

从 SQL Server 的下一个版本开始，我们终于可以跨行连接，而无需求助于任何变量或 XML 巫术。

STRING\_AGG (Transact-SQL)

不分组

SELECT STRING_AGG(Name, ', ') AS Departments
    FROM HumanResources.Department;

分组：

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
    FROM HumanResources.Department
    GROUP BY GroupName;

具有分组和子排序

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
    FROM HumanResources.Department
    GROUP BY GroupName;

`而且，与 CLR 解决方案不同，您可以控制排序。`

`STRING_AGG 似乎有 4000 个字符的显示限制`

`如果没有 GROUP BY 有没有办法进行排序（所以对于“无分组”示例）？`

`更新：我设法做到了以下几点，但有没有更清洁的方法？ SELECT STRING_AGG(Name, ', ') AS Departments FROM (SELECT TOP 100000 Name FROM HumanResources.Department ORDER BY Name) D;`

\`我必须将其转换为 NVarchar(max) 才能使其工作… \`\`\`SELECT STRING\_AGG(CAST(EmpName as NVARCHAR(MAX)), ‘,’) FROM EmpTable as t \`\`\`\`

## 答4: ##

> 与HuntsBot一起，探索全球自由职业机会–huntsbot.com

通过 SQL Server 中的 XML data() 命令尚未显示的一种方法是：

假设有一个名为 NameList 的表，其中有一列名为 FName，

SELECT FName + ', ' AS 'data()'
    FROM NameList
    FOR XML PATH('')

"Peter, Paul, Mary, "

只有多余的逗号必须处理。

正如@NReilingh 的评论所采用的那样，您可以使用以下方法删除尾随逗号。假设相同的表和列名：

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
    FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

`太棒了！当单独执行时，如您的示例所示，结果被格式化为超链接，单击时（在 SSMS 中）会打开一个包含数据的新窗口，但当用作更大查询的一部分时，它只会显示为字符串。它是一个字符串吗？还是我需要在将使用此数据的应用程序中区别对待的 xml？`

`这种方法还可以对 < 和 > 等字符进行 XML 转义。因此，选择 '' + FName + '' 会导致“JohnPaul...”`

`整洁的解决方案。我注意到即使我不添加 + ', ' 它仍然会在每个连接元素之间添加一个空格。`

`@Baodad 这似乎是交易的一部分。您可以通过替换添加的标记字符来解决问题。例如，这是一个完美的任何长度的逗号分隔列表：SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')`

`哇，实际上在我使用 data() 的测试中，替换的性能要好得多。超级诡异。`

## 答5: ##

> huntsbot.com提供全网独家一站式外包任务、远程工作、创意产品分享与订阅服务!

在 SQL Server 2005 中

SELECT Stuff(
      (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
      .value('text()[1]','nvarchar(max)'),1,2,N'')

在 SQL Server 2016 中

您可以使用 FOR JSON syntax

SELECT per.ID,
    Emails = JSON_VALUE(
       REPLACE(
         (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
        ,'"},{"_":"',', '),'$[0]._'
    ) 
    FROM Person per

结果会变成

Id  Emails
    1   abc@gmail.com
    2   NULL
    3   def@gmail.com, xyz@gmail.com

即使您的数据包含无效的 XML 字符，这也可以工作

‘“\},\{”*“:”’ 是安全的，因为如果您的数据包含 '“\},\{”*“:”’,，它将被转义到 “\},\{”\_“:”

您可以将 ', ’ 替换为任何字符串分隔符

而在 SQL Server 2017 中，Azure SQL 数据库

您可以使用新的 STRING\_AGG function

`很好地使用 STUFF 函数来消除前两个字符。`

`我最喜欢这个解决方案，因为我可以通过附加 'as ' 在选择列表中轻松使用它。我不确定如何使用@Ritesh 的解决方案来做到这一点。`

`这比接受的答案更好，因为此选项还处理未转义的 XML 保留字符，例如 <、>、& 等，FOR XML PATH('') 将自动转义。`

## 答6: ##

> huntsbot.com高效搞钱，一站式跟进超10+任务平台外包需求

在 MySQL 中，有一个函数 GROUP\_CONCAT()，它允许您连接多行的值。例子：

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
    FROM users 
    WHERE id IN (1,2,3) 
    GROUP BY a

`基本工作。需要考虑两件事：1）如果您的列不是 CHAR，则需要强制转换它，例如通过 GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',') 2）如果您有很多值，您应该增加 stackoverflow.com/a/1278210/1498405 中所写的 group_concat_max_len`

`截至 2022 年 3 月，这对我有用。我在行中有 url，并希望它们作为单列，这很有效。谢谢！`

`OP 是关于 [MS] SQL Server`

## 答7: ##

> huntsbot.com聚合了超过10+全球外包任务平台的外包需求，寻找外包任务与机会变的简单与高效。

使用 COALESCE - Learn more from here

例如：

102 103 104

然后在 SQL Server 中编写以下代码，

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
    SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
    FROM   TableName where Number IS NOT NULL
    
    SELECT @Numbers

输出将是：

102,103,104

`这确实是 IMO 的最佳解决方案，因为它避免了 FOR XML 呈现的编码问题。我使用了 Declare @Numbers AS Nvarchar(MAX)，效果很好。你能解释一下为什么你不建议使用它吗？`

`该解决方案已在 8 年前发布！ stackoverflow.com/a/194887/986862`

`为什么这个查询返回？？？符号而不是西里尔字母？这只是输出问题吗？`

`@EvilDr 您可以避免 XML 编码。请参阅：stackoverflow.com/questions/15643683/…`

`为什么不使用问题中的示例？`

## 答8: ##

> huntsbot.com提供全网独家一站式外包任务、远程工作、创意产品分享与订阅服务!

PostgreSQL 数组很棒。例子：

创建一些测试数据：

postgres=# \c test
    You are now connected to database "test" as user "hgimenez".
    test=# create table names (name text);
    CREATE TABLE
    test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
    INSERT 0 3
    test=# select * from names;
     name
    -------
     Peter
     Paul
     Mary
    (3 rows)

将它们聚合到一个数组中：

test=# select array_agg(name) from names;
     array_agg
    -------------------
     {
        Peter,Paul,Mary}
    (1 row)

将数组转换为逗号分隔的字符串：

test=# select array_to_string(array_agg(name), ', ') from names;
     array_to_string
    -------------------
     Peter, Paul, Mary
    (1 row)

完毕

从 PostgreSQL 9.0 开始就更容易了，引用“无名马”的已删除答案：

select string_agg(name, ',') 
    from names;

`如果您需要多列，例如括号中的员工 ID，请使用 concat 运算符：select array_to_string(array_agg(name||'('||id||')'`

`不适用于 sql-server，仅适用于 mysql`

## 答9: ##

> huntsbot.com – 高效赚钱，自由工作

Oracle 11g 第 2 版支持 LISTAGG 函数。文档 here。

COLUMN employees FORMAT A50
    
    SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
    FROM   emp
    GROUP BY deptno;
    
        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER
            20 ADAMS,FORD,JONES,SCOTT,SMITH
            30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
    
    3 rows selected.

警告

如果结果字符串有可能超过 4000 个字符，请小心执行此函数。它会抛出异常。如果是这种情况，那么您需要处理异常或滚动您自己的函数来防止连接的字符串超过 4000 个字符。

`对于旧版本的 Oracle，wm_concat 是完美的。 Alex 在链接礼物中解释了它的使用。谢谢亚历克斯！`

`LISTAGG 完美！只需阅读此处链接的文档。 wm_concat 从版本 12c 开始删除。`

## 答10: ##

> huntsbot.com精选全球7大洲远程工作机会，涵盖各领域，帮助想要远程工作的数字游民们能更精准、更高效的找到对方。

在 SQL Server 2005 及更高版本中，使用下面的查询来连接行。

DECLARE @t table
    (
        Id int,
        Name varchar(10)
    )
    INSERT INTO @t
    SELECT 1,'a' UNION ALL
    SELECT 1,'b' UNION ALL
    SELECT 2,'c' UNION ALL
    SELECT 2,'d' 
    
    SELECT ID,
    stuff(
    (
        SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
    ),1,1,'') 
    FROM (SELECT DISTINCT ID FROM @t ) t

`我相信当值包含 < 或 & 等 XML 符号时，这会失败。`

`与提供的示例一样工作得很好。我使用 CTE docs.microsoft.com/en-us/sql/t-sql/queries/… 而不是临时表或变量`

## 答11: ##

> huntsbot.com全球7大洲远程工作机会，探索不一样的工作方式

建议使用递归 CTE 解决方案，但未提供代码。下面的代码是递归 CTE 的示例。

请注意，尽管结果与问题匹配，但数据与给定的描述并不完全匹配，因为我假设您确实希望对行组执行此操作，而不是表中的所有行。将其更改为匹配表中的所有行留给读者作为练习。

;WITH basetable AS (
        SELECT
            id,
            CAST(name AS VARCHAR(MAX)) name,
            ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
            COUNT(*) OVER (Partition BY id) recs
        FROM (VALUES
            (1, 'Johnny', 1),
            (1, 'M', 2),
            (2, 'Bill', 1),
            (2, 'S.', 4),
            (2, 'Preston', 5),
            (2, 'Esq.', 6),
            (3, 'Ted', 1),
            (3, 'Theodore', 2),
            (3, 'Logan', 3),
            (4, 'Peter', 1),
            (4, 'Paul', 2),
            (4, 'Mary', 3)
        ) g (id, name, seq)
    ),
    rCTE AS (
        SELECT recs, id, name, rw
        FROM basetable
        WHERE rw = 1
    
        UNION ALL
    
        SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
        FROM basetable b
        INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
    )
    SELECT name
    FROM rCTE
    WHERE recs = rw AND ID=4

`对于大吃一惊：此查询将 12 行（3 列）插入到临时基表中，然后创建递归公用表表达式 (rCTE)，然后将 name 列展平为 4 个 groups< 的逗号分隔字符串/i> id 秒。乍一看，我认为这比大多数其他 SQL Server 解决方案所做的工作更多。`

`@knb：不确定这是赞美、谴责还是只是惊讶。基表是因为我喜欢我的示例实际工作，它与问题没有任何关系。`

原文链接：[https://www.huntsbot.com/qa/Pd7n/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?lang=zh\_CN&from=csdn][https_www.huntsbot.com_qa_Pd7n_how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv_lang_zh_CN_from_csdn]

> 保持自己快人一步,享受全网独家提供的一站式外包任务、远程工作、创意产品订阅服务–huntsbot.com

[https_www.huntsbot.com_qa_Pd7n_how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv_lang_zh_CN_from_csdn]: https://www.huntsbot.com/qa/Pd7n/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?lang=zh_CN&from=csdn