如何将多行组合到Oracle中以逗号分隔的列表中？

我有一个简单的查询:

select * from countries

结果如下:

country_name
------------
Albania
Andorra
Antigua
.....

我想在一行中返回结果,所以像这样:

Albania, Andorra, Antigua, ...

当然,我可以编写一个PL/SQL函数来完成这项工作(我已经在Oracle 10g中完成了),但是对于这个任务是否有更好的,最好是非特定于Oracle的解决方案(或者可能是内置函数) ？

我通常会用它来避免子查询中的多行,所以如果一个人有一个以上的公民身份,我不希望她/他在列表中是重复的.

我的问题是基于SQL Server 2005上的类似问题.

更新:我的功能如下:

CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;

JoshL.. 114

该WM_CONCAT函数(如果包含在数据库中,Oracle 11.2之前版本)或LISTAGG(启动Oracle 11.2)应该可以很好地完成.例如,这会在模式中获取以逗号分隔的表名列表:

select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

要么

select wm_concat(table_name) 
  from user_tables;

更多细节/选项

链接到文档

1> JoshL..：

---

该WM_CONCAT函数(如果包含在数据库中,Oracle 11.2之前版本)或LISTAGG(启动Oracle 11.2)应该可以很好地完成.例如,这会在模式中获取以逗号分隔的表名列表:

select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

要么

select wm_concat(table_name) 
  from user_tables;

更多细节/选项

链接到文档

---

WM_CONCAT在12c中被删除.使用此未记录功能的任何人在升级时都会感到惊讶.

---

请注意,Oracle不建议使用`WM_CONCAT`,因为它没有文档记录且不受支持:[WMSYS.WM_CONCAT不应用于客户应用程序,它是内部函数(Doc ID 1336219.1)](https://support.oracle. COM/CSP /主/条？CMD =显示&类型= NOT&DOCTYPE = HOWTO&ID = 1336219.1)

2> Daniel Emge..：

---

这是一种没有stragg或创建函数的简单方法.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

正如其他人所提到的,如果你使用11g R2或更高版本,你现在可以使用更简单的listagg.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

---

JoshL建议使用LISTAGG功能对于使用11.2或更新版本的人来说非常受欢迎.

---

只要确保你的连接结果不超过oracle数据库的VARCHAR2最大长度限制(很可能是4000字节),否则你会遇到`ORA-01489字符串连接的结果太长了`.

3> Makatun..：

---

对于Oracle,您可以使用LISTAGG

---

在JoshL指出的Oracle 11.2中.

4> 小智..：

---

你也可以使用它:

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;

5> 小智..：

---

你可以试试这个查询.

select listagg(country_name,',') within group (order by country_name) cnt 
from countries;