17,088
社区成员
发帖
与我相关
我的任务
分享
--1.创建测试表
create table tmp
as
select 1 seq, 1 linkid, '001' oltid, 'oport1' oltport, '42301' basid, 'bport1' basprot from dual union all
select 2 seq, 1 linkid, '42301' oltid, 'oport2' oltport, '002' basid, 'bport2' basprot from dual
--2.SQL实现
select linkid,
regexp_substr(str,'[^,]+',1,1) as oltid,
regexp_substr(str,'[^,]+',1,2) as oltport,
regexp_substr(str,'[^,]+',1,3) as switchid,
regexp_substr(str,'[^,]+',1,4) as switchport1,
regexp_substr(str,'[^,]+',1,5) as switchid2,
regexp_substr(str,'[^,]+',1,6) as switchport2,
regexp_substr(str,'[^,]+',1,7) as basid,
regexp_substr(str,'[^,]+',1,8) as basport
from(
select linkid,listagg(str,',') within group (order by seq) as str
from(select seq, linkid,oltid||','||oltport||','||basid||','||basprot as str from tmp)
group by linkid
);