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