考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
Oracle有两种方法:
create table name
(first_name varchar2(30));
insert into name values ('Peter');
insert into name values ('Paul');
insert into name values ('Mary');
解决方案是1:
select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
o/p=> Peter,Paul,Mary
解决方案是2:
select rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
o/p=> Peter,Paul,Mary
其他回答
我真的很喜欢Dana回答的优雅,只想让它变得完整。
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的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”])。
在PostgreSQL中-array_agg
SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;
或STRING_AGG
SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;
一个现成的解决方案,没有额外的逗号:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
空列表将导致NULL值。通常,您会将列表插入到表列或程序变量中:根据需要调整最大长度255。
(迪瓦卡尔和延斯·弗兰森提供了很好的答案,但需要改进。)
使用XML帮助我用逗号分隔行。对于额外的逗号,我们可以使用SQL Server的replace函数。使用AS“data()”将用空格连接行,而不是添加逗号,稍后可以用逗号替换,语法如下所示。
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')