如何在Oracle中将多行合并为逗号分隔列表？

Question

我有一个简单的查询：

select * from countries

以下是相应的结果：

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

我想将结果放在一行中，就像这样：

Albania, Andorra, Antigua, ...

当然，我可以编写一个PL/SQL函数来完成这项任务（我已经在Oracle 10g中完成了），但是否有更好、最好是非Oracle特定的解决方案（或内置函数）来完成此任务？

通常我会使用它来避免子查询中出现多个行，因此如果一个人有多个国籍，我不希望他/她在列表中重复。

我的问题基于类似于SQL Server 2005的问题：在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;

- rics

Answer 1

如果你的数据库包含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;

更多细节/选项

文档链接

- JoshL

---

这个命令比@Decci.7提供的快+1，而且我喜欢简单的一行代码 :D - Kitet

请注意，Oracle不建议使用WM_CONCAT，因为它是未记录和不受支持的：WMSYS.WM_CONCAT不应用于客户应用程序，它是一个内部函数（文档编号1336219.1）。 - Burhan Ali

WM_CONCAT在12c中已被废弃。升级后，任何使用这个未公开的函数的人都会有所惊讶。 - Jon Heller

listagg是完美的，但wm_concat在Oracle12中不起作用。 - gszecsenyi

Answer 2

以下是一种简单的方法，无需使用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.

- Daniel Emge

---

不错的简短解决方案，但有几个错别字影响了它。这一行应该是：FROM（SELECT country_name，ROW_NUMBER（）OVER（ORDER BY country_name）rn， - Stew S

对于使用11.2或更高版本的人来说，JoshL建议使用LISTAGG函数是非常可取的。 - JakeRobb

请确保您连接后的结果不超过Oracle数据库的VARCHAR2最大长度限制（很可能是4000字节），否则您将遇到“ORA-01489结果字符串串联太长”的错误。 - JanM

@JanM **[评论1/2]**，这就是我遇到的一些挑战。我使用 REGEXP_REPLACE 来删除重复项，但如果我先达到 VARCHAR2 的最大限制，则无法正常工作。 - datalifenyc

@JanM [评论2/2] 接下来，我尝试使用 RTRIM、XMLAGG、XMLELEMENT 和 GETCLOBVAL() 将其转换为 clob，然后将其强制转换回 VARCHAR2。但是，查询的运行时间变成了几个小时而不是 15 分钟。您有其他建议吗？此外，我看到有人建议创建一个自定义函数。 - datalifenyc

如果我连接两个表并想从第二个表中获取一个逗号分隔的列，那么这个查询如何工作？ - Basher Sarkar

Answer 3

对于 Oracle 数据库，您可以使用 LISTAGG 函数。

- Makatun

---

在Oracle 11.2中，正如JoshL所指出的那样。 - rics

链接损坏。 - user_che_ban

固定链接。谢谢。 - Makatun

Answer 4

你也可以使用这个：

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

- Decci.7

---

谢谢！这在Oracle 10g中有效。 - styfle

Answer 5

你可以尝试这个查询。

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

- Gaya3

---

Listagg在Oracle 11g Release 2中被引入。 - rics

如果列数太多，这种方法就行不通。 - iprashant

Answer 6

最快的方法是使用Oracle的collect函数。

您还可以这样做：

select *
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null

访问ask tom网站并搜索“stragg”或“string concatenation”，可以找到很多示例。还有一种未记录的Oracle函数可以满足您的需求。

- tuinstoel

Answer 7

在这个例子中，我们正在创建一个函数，将逗号分隔的不同行级别AP发票保留原因列表合并到一个标题级别查询字段中：

 FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN

  v_HoldReasons := ' ';

  FOR rHR IN v_HoldsCusror (p_InvoiceId)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END; 

- David MacIntosh

Answer 8

我需要类似的东西，找到了以下解决方案。

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from  

- tips

---

虽然它可以工作，但我不建议任何人使用这种解决方案。我曾经看到一个只有 80,000 行的表格使用此解决方案进行更新命令，而该命令执行了 6-8 个小时。 - csadam

@csadam，对于更大的行，您有什么建议？最终目标是删除重复项以避免varchar2 4000字节的限制。 - datalifenyc

@myidealab 你可以在这里找到一些解决方法（https://blogs.oracle.com/datawarehousing/managing-overflows-in-listagg）和（https://oracle-base.com/articles/misc/string-aggregation-techniques）。为了去除重复项，你可以尝试使用DISTINCT进行内部选择。也许最好的解决方案是为这些情况创建一个自定义函数...然而，你也可以重新设计你的解决方案，它真的需要使用一个4000个字符长的字符串吗？ - csadam

Answer 9

我总是不得不为此编写一些PL/SQL，或者只需将“，”连接到字段中并复制到编辑器中，然后从列表中删除CR，以获得单行。

也就是说，

select country_name||', ' country from countries

有点啰嗦。

如果你看看Ask Tom，你会看到很多可能的解决方案，但它们都会回归到类型声明和/或PL/SQL。

Ask Tom

- Andrew Wood

Answer 10

SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames

- user1626874