oracle 拆分类型为多行(regexp_substr) 与 多个行汇总合并(listagg(codecname,‘,‘)within group(order by endortype))到一个字段

–regexp_count(br_level, ‘.’) 统计字符.的个数
–regexp_substr(br_level, ‘[^.]+’, 1, level) br_level字段从位置1开始使用字符.拆分，level指取拆分后第level个字符串

–拆分批单批改类型 为多行
create table tmp_car_endor as
select * from (
with tmp_car as
(
select t.endorseno,t.endortype from prpphead t where t.endorseno in (select t2.endorseno from tmp_car_all t2)
),
endortype_level as(
select level lv from dual connect by level < 10
)
select t.endorseno,
a.lv,
regexp_count(t.endortype, ‘,’) + 1 as row_cnt,
regexp_substr(t.endortype, ‘[^,]+’, 1, a.lv) as endortype
from tmp_car t
inner join endortype_level a
on a.lv <= regexp_count(t.endortype, ‘,’) + 1
) mm order by mm.endortype
;

–将一个批单的多个批改类型名称汇总合并到一个字段
create table tmp_car_endortype_name as
select t.endorseno,listagg(codecname,‘,’)within group(order by endortype) as endortype_name
from tmp_car_endor t