logo标签列表

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

sqlsql-servercsvstring-concatenationgroup-concat
2398

考虑一个包含姓名的数据库表格，有三行：

Peter
Paul
Mary

有没有简单的方法将这些内容转换成一个字符串：Peter, Paul, Mary？
29
如需针对SQL Server的特定答案，请尝试此问题。 - Matt Hamilton
22
对于MySQL，请查看此答案中的Group_Concat。它可以将多行合并为一个字段。 - Pykler
31
希望SQL Server的下一个版本能够添加一项新功能，以优雅地解决多行字符串连接的问题，而不需要使用 FOR XML PATH 这种繁琐方式。 - Pete Alvin
4
不是SQL，但如果这只是一次性的事情，你可以将列表粘贴到这个在线工具convert.town/column-to-comma-separated-list中进行转换。 - Stack Man
4
在Oracle数据库中，你可以在11g r2版本之后使用LISTAGG(COLUMN_NAME)函数，它可以实现与旧版中不被支持的WM_CONCAT(COLUMN_NAME)函数相同的功能。 - Richard
显示剩余5条评论
48个回答
39

有人建议使用递归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
OPTION (MAXRECURSION 101)
5
对于惊讶的人：此查询将12行（3列）插入到临时基表中，然后创建一个递归公共表达式（rCTE），然后将“name”列展开为4个“id”组的逗号分隔字符串。乍一看，我认为这比SQL Server的大多数其他解决方案更繁琐。 - knb
5
不确定那是赞扬、谴责还是惊讶。“基础表格”只是因为我希望我的示例实际上能够工作，与问题本身没有太大关系。 - jmoreno
1
我真的很喜欢这个解决方案（迭代是人性的，递归是神圣的！），但如果要连接100个或更多条目，则会在SQL Server中达到递归限制而自动燃烧。这对不谨慎的人来说是一个地雷... - RET
1
@RET：很好的观点，它是可调整的，但应该被提及。 - jmoreno
37

我在家里没有访问SQL Server的权限，所以我猜想语法，但大致如下：

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names
13
你需要将 @names 初始化为非空值，否则你会一直得到 NULL；同时你还需要处理分隔符（包括不必要的分隔符）。 - Marc Gravell
5
这种方法的唯一问题（我经常使用）是你无法嵌入它。 - ekkis
3
为了消除前导空格，请将查询更改为 SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names。 - Tian van Heerden
此外，您必须检查Name不为空，可以通过执行以下操作来实现：SELECT @names = @names + ISNULL(' ' + Name, '') - Vita1ij
35
在 SQL Server 2017 或更高版本中，您可以使用 STRING_AGG() 函数生成逗号分隔的值。请看下面的一个例子。
SELECT
    VendorId, STRING_AGG(FirstName,',') UsersName
FROM Users
WHERE VendorId != 9
GROUP BY VendorId

Enter image description here

比起旧的 XML 解决方案，这个简单多了 - 感谢分享！ - whytheq
29

您需要创建一个变量来保存最终结果，并选择它，就像这样。

最简单的解决方案

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;
24
在 SQL Server 的下一个版本中，将会内置 STRING_AGG 函数。请在《STRING_AGG(Transact-SQL)》中了解更多相关信息。STRING_AGG (Transact-SQL)。
24
这对我有用（SQL Server 2016）：
SELECT CarNamesString = STUFF((
         SELECT ',' + [Name]
            FROM tbl_cars
            FOR XML PATH('')
         ), 1, 1, '')

这是源代码：https://www.mytecbits.com/ 对于较新的SQL版本（终于实现了）
SELECT STRING_AGG(Name, ', ') AS CarNames
FROM tbl_TypeCar;

而对于MySQL的解决方案（因为这个页面在Google上显示为MySQL）：
SELECT [Name],
       GROUP_CONCAT(DISTINCT [Name]  SEPARATOR ',')
       FROM tbl_cars

从MySQL文档中。
22

一个可直接使用的解决方案，不需要额外添加逗号：

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

空列表将导致NULL值。

通常，您会将列表插入到表列或程序变量中：将255最大长度调整为您的需要。

（Diwakar和Jens Frandsen提供了不错的答案，但需要改进。）

使用此符号时，冒号前面应该有一个空格 :( - slayernoah
2
如果您不想要额外的空格，请将“，”替换为“，”。 - Daniel Reis
19

使用XML帮助我将行分隔为逗号。对于多余的逗号，我们可以使用SQL Server的替换函数。不是添加逗号，而是使用AS 'data（）'将行连接起来，并在稍后将其用逗号替换，如下所示：

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ')
2
在我看来，这是最好的答案。当您需要加入另一个表时，声明变量的使用不好，而这种方法简洁明了。干得好。 - David Roussel
8
如果FName数据已经含有空格，比如"My Name"，那么这样做效果不佳。 - binball
真的在我的 MS-SQL 2016 上运行正常 选择 REPLACE( (select Name AS 'data()' from Brand Where Id IN (1,2,3,4) for xml path('')) , ' ', ', ') as allBrands - Rejwanul Reja
18
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

这里有一个示例：

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
12

除本答案外，读者需要了解特定领域的表格，例如车辆或学生。必须创建并填充数据以测试解决方案。

下面是一个示例，使用 SQL Server 的 "Information_Schema.Columns" 表。通过使用此解决方案，无需创建表格或添加数据。此示例为数据库中所有表格创建逗号分隔的列名列表。

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接