如何在Oracle中将多行合并为逗号分隔的列表？ [重复]

我有一个简单的查询：

select * from countries

，结果如下：

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

I想要返回结果的一行，所以是这样的：

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;

#1 楼

这是不使用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.

不错的简短解决方案，但有一些错别字给它造成了损害。该行应显示为：FROM（SELECT country_name，ROW_NUMBER（）OVER（ORDER BY country_name）rn，

–炖S
09年1月26日在21:26

对于使用11.2或更高版本的用户，JoshL建议使用LISTAGG函数。

– JakeRobb
15年6月8日在21:10

只要确保您的串联结果不超过oracle数据库的VARCHAR2最大长度限制（很可能是4000字节），否则您将遇到ORA-01489字符串串联的结果太长。

– JanM
17年6月21日在9:37

@JanM [评论2之1]因此，这就是我遇到的一些挑战。我使用REGEXP_REPLACE删除重复项，但是如果我先达到VARCHAR2最大限制，则此方法将不起作用。

– datalifenyc
19年1月28日23:37

@JanM [注释2之2]接下来，我尝试使用RTRIM，XMLAGG，XMLELEMENT和GETCLOBVAL（）将其转换为Clob，然后将其转换回VARCHAR2。但是，查询的运行时间变为小时而不是15分钟。您对其他方法有什么建议吗？另外，我看到了有关创建自定义函数的建议。

– datalifenyc
19年1月28日23:37

#2 楼

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;

更多详细信息/选项

链接到文档

该命令比@ Decci.7提供+1的速度快，我喜欢简单的一线式：D

– Kitet
2014-02-17 11:07

请注意，Oracle不建议使用WM_CONCAT，因为它没有记录且不受支持：WMSYS.WM_CONCAT不应用于客户应用程序，它是一种内部功能（文档ID 1336219.1）

– Burhan Ali
2014年3月24日10:22

WM_CONCAT在12c中掉线。使用此未公开功能的任何人升级时都会感到惊讶。

–乔恩·海勒（Jon Heller）
2014年5月9日在6:24

listagg是完美的，但是wm_concat不适用于Oracle12

– gszecsenyi
17-10-17在8:53

#3 楼

对于Oracle，您可以使用LISTAGG

JoshL指出，在Oracle 11.2中。

–rics
2011年8月25日上午8:27

链接断开。

–果馅奶酪卷
19年4月8日在10:31

#4 楼

您也可以使用它：

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

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

–样式
15年5月12日在17:39

#5 楼

您可以尝试此查询。

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

在Oracle 11g第2版中引入了Listagg。

–rics
2014年3月20日在10:46

如果列太多，这将不起作用。

–令人讨厌
16年11月17日在18:56

#6 楼

最快的方法是使用Oracle收集功能。

您还可以执行以下操作：

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

访问该网站询问tom并搜索“ stragg”或“ string concatenation”。许多
示例。还有一个未记录的oracle函数可以满足您的需求。

#7 楼

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

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

虽然有效，但我不建议任何人使用此解决方案。我使用此解决方案在只有80 000行的表上看到了一条更新命令，该命令运行了6-8个小时。

– csadam
2011年10月24日16:06

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

– datalifenyc
19年1月29日，下午3:11

@myidealab您可以在此处和此处找到一些解决方法。要删除重复项，您可以尝试使用DISTINCT进行内部选择。也许最好的解决方案是为这些情况创建一个自定义函数...但是，您也可以重新设计解决方案，它真的必须使用4000个字符长的字符串吗？

– csadam
19年1月30日在18:58

#8 楼

在此示例中，我们正在创建一个函数，以逗号分隔的行级别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;

#9 楼

我一直为此必须编写一些PL / SQL，或者我只是将一个'，'连接到该字段，然后复制到编辑器中，并从列表中删除CR，只给我一行。

是，

select country_name||', ' country from countries

双向缠绕有点长。

如果你问Ask Tom，你会看到很多可行的解决方案，但是它们全部恢复为类型声明和/或PL / SQL

询问Tom

#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

#11 楼

您可以使用此查询完成上述任务

DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test

有关详细信息和逐步说明，请访问以下链接http://oops-solution.blogspot.com/2011 /11/sql-server-convert-table-column-data.html

编程黑洞网