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

Peter
Paul
Mary

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


当前回答

一个现成的解决方案,没有额外的逗号:

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

空列表将导致NULL值。通常,您会将列表插入到表列或程序变量中:根据需要调整最大长度255。

(迪瓦卡尔和延斯·弗兰森提供了很好的答案,但需要改进。)

其他回答

使用此项:

ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'

其中“300”可以是任何宽度,考虑到您认为将显示的最大项目数。

在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语法

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函数

对于Oracle DB,请参阅以下问题:如何在Oracle中将多行连接成一行而不创建存储过程?

最好的答案似乎是@Emmanuel,使用了内置的LISTAGG()函数,该函数在Oracle 11g Release 2和更高版本中提供。

SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

正如@user762952所指出的,根据Oracle的文档http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php,WM_CONCAT()函数也是一个选项。它看起来很稳定,但Oracle明确建议不要将它用于任何应用程序SQL,因此使用时风险自负。

除此之外,您还必须编写自己的函数;上面的Oracle文档提供了如何做到这一点的指南。

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

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ') 
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