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
left join prpdcode t2 on t.endortype = t2.codecode
where t2.codetype=‘EndorType’
group by t.endorseno
;

引用别人的方法：
–2. 多条数据拆分
–1、方法1
–正确
with z_branch as(
select ‘BR1001’ brid, ‘BR1001.BR1002.BR1003’ br_level from dual
union all
select ‘BR1002’ brid, ‘BR1002.BR1003’ br_level from dual
union all
select ‘BR1003’ brid, ‘BR1001.BR1003’ br_level from dual
)
select brid,
level,
regexp_count(br_level, ‘.’) + 1 as row_cnt,
regexp_substr(br_level, ‘[^.]+’, 1, level) as s_brid
from z_branch t
connect by level <= regexp_count(br_level, ‘.’) + 1
and t.brid = prior t.brid
and prior dbms_random.value > 0
;

–2、方法2
–正确 借助伪列
with z_branch as(
select ‘BR1001’ brid, ‘BR1001.BR1002.BR1003’ br_level from dual
union all
select ‘BR1002’ brid, ‘BR1002.BR1003’ br_level from dual
union all
select ‘BR1003’ brid, ‘BR1001.BR1003’ br_level from dual
),
z_level as(
select level lv from dual connect by level < 10
)
select t.brid,
a.lv,
regexp_count(t.br_level, ‘.’) + 1 as row_cnt,
regexp_substr(t.br_level, ‘[^.]+’, 1, a.lv) as s_brid
from z_branch t
inner join z_level a
on a.lv <= regexp_count(t.br_level, ‘.’) + 1;