script - ¿Cómo usar la función LISTAGG de Oracle con un filtro único?

script awr oracle

Question

Esta pregunta ya tiene una respuesta aquí:

• LISTAGG en oráculo para devolver valores distintos 19 respuestas

Tengo una mesa como esta:

group_id name
-------- ----
1 David
1 John
1 Alan
1 David
2 Julie
2 Charles

Y quiero el siguiente resultado:

group_id names
-------- -----
1 ''Alan, David, John''
2 ''Charles, Julie''

Puedo usar la siguiente consulta:

select group_id,
listagg(name, '','') within group (order by name) as names
from demotable
group by group_id

Para obtener esto (resultado muy similar):

group_id names
-------- -----
1 ''Alan, David, David, John''
2 ''Charles, Julie''

¿Alguna idea de cómo puedo filtrar los nombres por singularidad en la llamada LISTAGG ?

Answer 1

En 11g puedes usar la función no documentada wm_concat de la siguiente manera:

 select wm_concat(distinct name) as names from demotable group by group_id

Answer 2

Necesitaba esta paz de código como una subconsulta con algún filtro de datos antes de la agregación basada en la consulta más externa, pero no pude hacer esto usando el código de respuesta elegido porque este filtro debe ir en la selección más interna (consulta de tercer nivel) y los parámetros de filtro estaban en la selección más externa (consulta de primer nivel), lo que me dio el error ORA-00904: "TB_OUTERMOST". "COL": identificador no válido como el SQL ANSI indica que las referencias de tabla (nombres de correlación) tienen un alcance solo un nivel profundo.

Necesitaba una solución sin niveles de subconsulta y esta a continuación me funcionó muy bien:

with
demotable as
(
select 1 group_id, ''David'' name from dual union all
select 1 group_id, ''John'' name from dual union all
select 1 group_id, ''Alan'' name from dual union all
select 1 group_id, ''David'' name from dual union all
select 2 group_id, ''Julie'' name from dual union all
select 2 group_id, ''Charlie'' name from dual
)
select distinct
group_id,
listagg(name, '','') within group (order by name) over (partition by group_id) names
from demotable
-- where any filter I want
group by group_id, name
order by group_id;

Answer 3

No tengo una instancia de 11g disponible hoy, pero podría no usar:

SELECT group_id,
LISTAGG(name, '','') WITHIN GROUP (ORDER BY name) AS names
FROM (
SELECT UNIQUE
group_id,
name
FROM demotable
)
GROUP BY group_id

Answer 4

Respuesta súper simple - ¡resuelto!

select group_id,
regexp_replace(
listagg(name, '','') within group (order by name)
,''([^,]+)(,/1)*(,|$)'', ''/1/3'')
from demotable
group by group_id;

Esto solo funciona si especifica el delimitador en '','' no '','' es decir, funciona solo sin espacios después de la coma. Si desea espacios después de la coma, aquí hay un ejemplo de cómo.

select
replace(
regexp_replace(
regexp_replace(''BBall, BBall, BBall, Football, Ice Hockey '','',/s*'','','')
,''([^,]+)(,/1)*(,|$)'', ''/1/3'')
,'','','', '')
from dual

da BBall, fútbol, ​​hockey sobre hielo

mi respuesta completa here

Answer 5

a continuación no está documentado y no recomendado por Oracle. y no puede aplicar en función, muestra error

select wm_concat(distinct name) as names from demotable group by group_id

recuerdos zia

Answer 6

create table demotable(group_id number, name varchar2(100));
insert into demotable values(1,''David'');
insert into demotable values(1,''John'');
insert into demotable values(1,''Alan'');
insert into demotable values(1,''David'');
insert into demotable values(2,''Julie'');
insert into demotable values(2,''Charles'');
commit;
select group_id,
(select listagg(column_value, '','') within group (order by column_value) from table(coll_names)) as names
from (
select group_id, collect(distinct name) as coll_names
from demotable
group by group_id
)
GROUP_ID NAMES
1 Alan,David,John
2 Charles,Julie

Answer 7

select group_id,
listagg(name, '','') within group (order by name) as names
over (partition by group_id)
from demotable
group by group_id