oracle 行转列

发布网友发布时间：2022-04-22 20:42

共2个回答

热心网友时间：2022-04-08 07:02

测试表测了下，可以用，你看看，主要是先根据逗号进行分割，然后connect，level等于逗号的数目：
select id,nvl(substr(glbh,instr(glbh,',',1,lvl)+1,instr(glbh,',',1,lvl+1)-instr(glbh,',',1,lvl)-1),'kong') glbh
from (
select id,lvl,','||glbh||',' glbh
from test a ,
(select level lvl from al connect by level<=
(select max(length(glbh)-length(replace(glbh,',')))+1 from test)) b
) t1
where substr(glbh,instr(glbh,',',1,lvl)+1,instr(glbh,',',1,lvl+1)-instr(glbh,',',1,lvl)-1) is not null
order by id,glbh;

热心网友时间：2022-04-08 08:20

with t as
(
select '12' a,'103,104,105,106,107' b from al
union all
select '13' a,'109,110,111' b from al
)
select distinct * from (
select a,substr(b,instr(b,',',level)+1,3) pos
from t
connect by level <length(b)

) order by a