如何在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.
我的问题是基于 更新: 我的功能如下:
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;
推荐答案
这是不使用stragg或创建函数的简单方法.
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屋!