考虑一个包含名称的数据库表,其中有三行:

Peter
Paul
Mary

有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?


当前回答

在SQLServer2005及更高版本中,使用下面的查询连接行。

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

其他回答

我并没有做过任何关于性能的分析,因为我的列表中只有不到10项,但在查看了30多个答案后,我感到很惊讶,我仍然对已经给出的类似答案进行了修改,类似于对单个组列表使用COALESCE,甚至不必设置我的变量(无论如何默认为NULL),并且它假设我的源数据表中的所有条目都是非空的:

DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData

我确信COALENCE内部使用了相同的想法。让我们希望微软不会在我身上改变这一点。

此答案需要服务器上的某些权限才能工作。

装配是一个很好的选择。有很多网站解释如何创建它。我认为解释得很好的就是这个。

如果您愿意,我已经创建了程序集,可以在这里下载DLL文件。

下载后,需要在SQL Server中运行以下脚本:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

CREATE Assembly concat_assembly
   AUTHORIZATION dbo
   FROM '<PATH TO Concat.dll IN SERVER>'
   WITH PERMISSION_SET = SAFE;
GO

CREATE AGGREGATE dbo.concat (

    @Value NVARCHAR(MAX)
  , @Delimiter NVARCHAR(4000)

) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO

sp_configure 'clr enabled', 1;
RECONFIGURE

请注意,服务器可以访问程序集的路径。由于您已成功完成所有步骤,因此可以使用以下功能:

SELECT dbo.Concat(field1, ',')
FROM Table1

自SQL Server 2017以来,可以使用STRING_AGG函数。

Oracle 11g Release 2支持LISTAGG功能。此处的文档。

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个字符。

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

最简单的解决方案

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

使用XML帮助我用逗号分隔行。对于额外的逗号,我们可以使用SQL Server的replace函数。使用AS“data()”将用空格连接行,而不是添加逗号,稍后可以用逗号替换,语法如下所示。

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ')