如何在 Oracle 中将多行组合成逗号分隔的列表? [英] How can I combine multiple rows into a comma-delimited list in Oracle?

问题描述

我有一个简单的查询:

select * from countries

结果如下:

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

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

I would like to return the results in one row, so like this:

Albania, Andorra, Antigua, ...

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

Of course, I can write a PL/SQL function to do the job (I already did in Oracle 10g), but is there a nicer, preferably non-Oracle-specific solution (or may be a built-in function) for this task?

我通常会使用它来避免子查询中的多行，因此如果一个人拥有多个公民身份，我不希望她/他在列表中重复.

I would generally use it to avoid multiple rows in a sub-query, so if a person has more then one citizenship, I do not want her/him to be a duplicate in the list.

我的问题基于 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;

推荐答案

这里有一个简单的方法，无需拖延或创建函数.

Here is a simple way without stragg or creating a function.

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.

As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

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

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

1 row selected.

这篇关于如何在 Oracle 中将多行组合成逗号分隔的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！