I have a query something like this:
select listagg(tab2.surna||' '||tab2.name||':'||tab2.addr||' '||tab2.numb,', ') within group( order by tab2.name)
where tab1.id=tab2.id2id /*join tab1 with tab2 */
I think you need 2 levels of
listagg for that. As you didn't provide any script to replicate your data structure, I provide a generic example of my own...
with tab as ( select 's' s,'n' n, 'addr1' addr from dual union all select 's' s,'n' n, 'addr2' addr from dual union all select 'd' s,'k' n, 'addr3' addr from dual union all select 'd' s,'k' n, 'addr4' addr from dual ) select listagg(res,',') within group (order by res) final_res from ( select s || n || ':(' ||listagg( addr,', ') within group (order by s,n) || ')'res from tab group by s||n )
dk:(addr3, addr4),sn:(addr1, addr2)