¿Cómo puedo combinar varias filas en una lista delimitada por comas en Oracle? [duplicar]
Tengo una consulta sencilla:
select * from countries
con los siguientes resultados:
country_name
------------
Albania
Andorra
Antigua
.....
Me gustaría devolver los resultados en una fila, así:
Albania, Andorra, Antigua, ...
Por supuesto, puedo escribir una función PL/SQL para hacer el trabajo (ya lo hice en Oracle 10g), pero ¿existe una solución mejor, preferiblemente no específica de Oracle (o puede ser una función incorporada) para esta tarea? ?
Generalmente lo usaría para evitar múltiples filas en una subconsulta, por lo que si una persona tiene más de una ciudadanía, no quiero que esté duplicado en la lista.
Mi pregunta se basa en una pregunta similar sobre SQL Server 2005 .
ACTUALIZACIÓN : Mi función se ve así:
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;
La WM_CONCATfunción (si está incluida en su base de datos, antes de Oracle 11.2) o LISTAGG(a partir de Oracle 11.2) debería funcionar bien. Por ejemplo, esto obtiene una lista delimitada por comas de los nombres de las tablas en su esquema:
select listagg(table_name, ', ') within group (order by table_name)
from user_tables;
o
select wm_concat(table_name)
from user_tables;
Más detalles/opciones
Enlace a la documentación
Aquí hay una manera simple sin desviarse ni crear una función.
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.
Como otros han mencionado, si tiene 11g R2 o superior, ahora puede usar listagg, que es mucho más simple.
select listagg(country_name,', ') within group(order by country_name) csv
from countries;
CSV
--------------------------
Albania, Andorra, Antigua
1 row selected.
Para Oracle puedes usar LISTAGG
puedes probar esta consulta.
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
Puedes usar esto también:
SELECT RTRIM (
XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
',')
country_name
FROM countries;
