oracle 列拆分关联查询和查询后的列组合

-----------------------建表-------------------------
create table test(id int, plist varchar2(30)) ;

create table p(pid int ,pname varchar2(10));

-----------------------插入测试数据----------------------------
insert into test values(1,'28345|39262|56214');
insert into test values(2,'28345|56214');
insert into test values(3,'56214');

insert into p values(28345,'产品A');
insert into p values(39262,'产品B');
insert into p values(56214,'产品C');

 

-----------------------------拆分语句及结果------------------------------------

 
select id, plist,level p_level, regexp_substr(plist , '[^|]+', 1, level) pid
from test
connect by level <= regexp_count(plist , '[^|]+')
and prior id = id
and prior dbms_random.value is not null

查询结果

 

 

 

-------------------拆分后关联并拼接字符后的处理语句-------------------

with m as (

--拆分列数据
select id, plist,level p_level, regexp_substr(plist , '[^|]+', 1, level) pid
from test
connect by level <= regexp_count(plist , '[^|]+')
and prior id = id
and prior dbms_random.value is not null  
)
select m.id , m.plist, listagg(p.pname,',') within group(order by p_level) rrr
from m inner join p on m.pid = p.pid
group by m.id, m.plist ;

DROP TABLE test;
DROP TABLE P;

 

实战亲测可用

with m as
 (select nsp.id,
         nsp.performance_no,
         nsp.spot_manager_panid,
         level p_level,
         regexp_substr(nsp.spot_manager_panid, '[^,]+', 1, level) as pid
    from nh_sale_performance nsp
   start with nsp.spot_manager_panid like '%,%'
          and nsp.performance_no is not null
  connect by level <= regexp_count(nsp.spot_manager_panid, '[^,]+')
         and prior nsp.id = nsp.id
         and prior dbms_random.value is not null)
select m.performance_no,
       m.spot_manager_panid,
       listagg(su.old_user_id, ',') within group(order by p_level) rrr
  from m
 inner join sys_user su
    on su.id = m.pid
 group by m.id, m.spot_manager_panid, performance_no