¿Cómo puedo combinar varias filas en una lista delimitada por comas en Oracle? [duplicar]
Esta pregunta ya tiene una respuesta aquí:
Tengo una simple consulta:
select * from countries
Con los siguientes resultados:
country_name
------------
Albania
Andorra
Antigua
.....
Me gustaría devolver los resultados en una fila, así que así:
Albania, Andorra, Antigua, ...
Por supuesto, puedo escribir una función PL / SQL para hacer el trabajo (ya lo hice en Oracle 10g), pero ¿hay 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 sub-consulta, por lo que si una persona tiene más de una ciudadanía, no quiero que sea un duplicado en la lista.
Mi pregunta se basa en la pregunta similar en 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;
11 answers
Aquí hay una manera simple sin stragg o 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 está en 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-02 19:18:49
La función WM_CONCAT
(si está incluida en su base de datos, pre Oracle 11.2) o LISTAGG
(a partir de Oracle 11.2) debería hacer el truco muy bien. Por ejemplo, esto obtiene una lista delimitada por comas de los nombres de tabla 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-11 12:39:52
Para Oracle puede usar LISTAGG
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-30 10:28:40
Puede probar esta consulta.
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-15 13:33:32
También puedes usar esto:
SELECT RTRIM (
XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
',')
country_name
FROM countries;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-18 04:09:05
La forma más rápida es usar la función Oracle collect.
También puedes hacer esto:
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
Visite el sitio pregunte a tom y busque en 'stragg' o 'concatenación de cadenas' . Un montón de ejemplos. También hay una función oracle no documentada para satisfacer sus necesidades.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-22 13:10:05
Necesitaba algo similar y encontré la siguiente solución.
select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-10 18:34:16
En este ejemplo estamos creando una función para traer una lista delineada por comas de distintas razones de retención de facturas de AP de nivel de línea en un campo para la consulta de nivel de encabezado:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-11-16 22:27:47
Siempre he tenido que escribir algún PL/SQL para esto o simplemente concateno un ',' al campo y copio en un editor y elimino el CR de la lista dándome la sola línea.
Es decir,
select country_name||', ' country from countries
Un poco largo en ambos sentidos.
Si nos fijamos en Ask Tom, verá un montón de posibles soluciones, pero todas vuelven a las declaraciones de tipo y/o PL/SQL
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-10 18:32:00
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-08-27 13:02:35
Puede usar esta consulta para realizar la tarea anterior
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test
Para obtener detalles y una explicación paso a paso, visite lo siguiente link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-08 20:11:02