SQL> select deptno,
  2    replace( replace( replace(
  3      XMLQUERY('for $cc in ora:view("emp")
let $ename:=$cc/ROW/ENAME/text()
where $cc/ROW/DEPTNO/number()=$deptno/d/number()
return <e>{$ename}</e>'
  4      passing by value xmltype('<d>'||deptno||'</d>') as "deptno"
  5      returning content
  6    ),'</e><e>', ','),'<e>'),'</e>') enames
  7  from dept
    DEPTNO ENAMES
---------- --------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        40
 

create table KeyList(

Key char(3),

Val char(2),

primary key (Key,Val));

insert into KeyList values('AAA','12');

insert into KeyList values('AAA','2A');

insert into KeyList values('AAA','32');

insert into KeyList values('AAA','44');

insert into KeyList values('BBB','11');

insert into KeyList values('BBB','33');

insert into KeyList values('BBB','8S');

commit;

col ConcatVal for a20

select Key,

substr(

replace(

    replace(XMLAgg(XMLElement("dummy",Val) order by Val),'</dummy>'),

    '<dummy>',',')

,2) as ConcatVal

from KeyList

group by Key;

select Key,max(SubStr(sys_connect_by_path(Val,','),2)) as ConcatVal

from (select Key,Val,

      Lag(RowID) over(partition by Key order by Val) as LagRowID

      from KeyList)

start with LagRowID is null

connect by Prior RowID = LagRowID

group by Key;