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

Peter
Paul
Mary

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


当前回答

这也很有用

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

回报

Peter,Paul,Mary

其他回答

此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的For XML PATH方法之一。

使用COALENCE:

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

只是一些解释(因为这个答案似乎得到了相对规律的观点):

联合实际上只是一种有助于实现两件事的欺骗:

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

or:

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

取决于您想要的行为(第一个选项只是过滤掉NULL,第二个选项用标记消息将它们保留在列表中[用适合您的内容替换“N/a”])。

在Oracle中,它是wm_concat。我相信这个功能在10g及更高版本中可用。

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

使用递归查询,您可以执行以下操作:

-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;

-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');

-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank  where rang=1
union all
select f0.*,  cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName 
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1 
)
select AllName from tmpRecursive
where rang=NbRow

在MySQL中,有一个函数GROUP_CONCATT(),它允许您连接多行的值。例子:

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