如何在 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.
相关文章