查询sql有多个行转列，怎么写？多个listagg+正则去重太长了

[-一个大坑]

select s.deriveno,
               regexp_replace(listagg(m.appno, ',') within
                              group(order by m.appno),
                              '([^,]+)(,\1)+',
                              '\1') as appno,
               regexp_replace(listagg(cg.deptid, ',') within
                              group(order by cg.chargeseq),
                              '([^,]+)(,\1)+',
                              '\1') as deptid,
               regexp_replace(listagg(p.pname || '/' || p.travel_no, ',')
                              within group(order by p.appseq),
                              '([^,]+)(,\1)+',
                              '\1') as pname,
               s.carno,
               m.ride_schedule,
               regexp_replace(listagg(m.appemplname||'/'||e.emplid, ',') within
                              group(order by m.appno),
                              '([^,]+)(,\1)+',
                              '\1') as appemplname,
               m.appdt,
               m.ride_starttime,
               m.ride_backtime,
               g.groupname,
               g.groupscope,
               regexp_replace(listagg(g.aiml1 || '/'|| g.aiml2 || '/' || g.aiml3, ',') within
                              group(order by g.aim_id),
                              '([^,]+)(,\1)+',
                              '\1') as aim_id,
               regexp_replace(listagg(sc.cityfrom || sc.addfrom, ',') within
                              group(order by sc.city_seq),
                              '([^,]+)(,\1)+',
                              '\1') as addfrom,
               case (regexp_replace(listagg(sc.cityfrom, ',') within
                                group(order by sc.cityfrom),
                                '([^,]+)(,\1)+',
                                '\1'))
                 when '深圳市' then
                  '市內'
                 else
                  '市外'
               end as cityfrom
          from app_send       s,
               app_nomapping  n,
               app_main       m,
               employee       e,
               bd_groupandaim g,
               app_schedule   sc,
               app_chargedept cg,
               app_passenger  p
         where s.deriveno = n.deriveno
           and m.appno = n.appno
           and m.appemplid = e.emplid
           and m.aim_id = g.aim_id
           and m.appno = sc.appno
           and s.deriveno = cg.appno
           and n.optype in ('SPE', 'SEP')
           and p.appno = s.deriveno
         group by m.ride_starttime,
                  s.deriveno,
                  s.carno,
                  m.ride_schedule,
                  m.ride_backtime,
                  m.appdt,
                  g.groupname,
                  g.groupscope

[-一个大坑]

主要是按单号分，单号不一样的都合在一行，只是有的是一样，不用合并

[chengccy]

就是拼接字符串去重咯? 三个办法, 1.自己写一个 instr+substr的函数对字符串去重; 2.自己写一个支持去重listagg (自定义聚合函数); 3. wm_concat支持distinct , 但是在PLSQL只能在动态SQL使用

[chengccy]

Oracle 的正则的效率不如 instr+substr