listagg单组分组查询整组拼接字段

with orguser as(
     select '2' as userid from dual
     union select '1' as userid from dual
     union select '3' as userid from dual
)
select 
     userid,
     listagg(FOLDERNAME, '/') within group(order by length(FOLDERID)) as FOLDERNAME 
from (
  select dl.FOLDERNAME,ud.userid,dl.FOLDERID from orgdeptlist dl right join (
    select ud.userid,ud.FOLDERID from orguserdeptmap ud join orguser ui
    on ui.userid = ud.userid
  ) ud on dl.FOLDERID in(substr(ud.FOLDERID || '', 0, 6), 
  substr(ud.FOLDERID || '', 0, 9), 
  substr(ud.FOLDERID || '', 0, 12), substr(ud.FOLDERID || '', 0, 15))
)
group by userid;

查询结果

USERID	FOLDERNAME
1	xxxx/xxxx/xxx
2	xxxx
2	xxxx/xxx

--with as 相当于一张临时表(一次分析，多次使用)  
with temp as
 (select 'male' as sex, 'zhangsan' as stu_name from dual 
         union select 'male' as sex,'lisi' as stu_name from dual 
         union select 'female' as sex, 'xiaohong' as stu_name from dual 
         union select 'female' as sex,'xiaozhang' as stu_name from dual 
         union select 'male' as sex,'zhaoliu' as stu_name from dual
)
---select * from temp;
--第二种方法: 使用listagg()方法，可以指定分隔的字符
select t.sex as sex,
       listagg(t.stu_name, ',') within group(order by t.stu_name) as stu_name 
from temp t 
group by t.sex;