Oracle - JOIN CON CADENA DELIMITADA POR COMAS

create table tablax

(

  id integer,

  campo1 varchar2(100),

  local varchar2(30),

  ids varchar2(50)

) ;

insert into tablax (id,campo1,local,ids) values(1,'Alejandro','AAA','1,2') ;

insert into tablax (id,campo1,local,ids) values(2,'Manuel','BBB','1,2,3') ;

insert into tablax (id,campo1,local,ids) values(3,'Roberto','CCC','3') ;

commit ;

select local, upper((select listagg(campo1,' and ') within group (order by campo1) from tablax where x.ids like '%'||id||'%' )) visitadores from tablax x;

create table tablax

(

  id integer,

  campo1 varchar2(100),

  local varchar2(30),

  ids varchar2(50)

) ;

create unique index tablax_pk on tablax (id) ;

insert into tablax (id,campo1,local,ids) values(1,'Alejandro','AAA','1,2') ;

insert into tablax (id,campo1,local,ids) values(2,'Manuel','BBB','1,2,3') ;

insert into tablax (id,campo1,local,ids) values(3,'Roberto','CCC','3') ;

commit ;

-- SOLUCION --

with t1 as (

select id, to_number(replace(substr(ids,1,instr(ids,',',1,2)),',','')) idn

from

(

  select unique id, substr(ids,instr(ids,',',1,level)) ids

  from

  (

    select id, ','||ids||',' ids, length(ids)-length(replace(ids,',',''))+1 cant

    from tablax

  )

  connect by level <= cant

) )

select

    local,

    UPPER(

      (select

           listagg(campo1,' and ') within group (order by campo1)

         from tablax

         where id in (select idn from t1 where id=x.id))) visitadores

  from tablax x ;