17,078
社区成员
发帖
与我相关
我的任务
分享
select *
from (select t.id,
t.姓名,
column_value as s_id,
regexp_substr(电话, '[^;]+', 1, column_value) as 电话
from tmp t,
table(cast(multiset
(select level
from dual
connect by level <= regexp_count(电话, ';') + 1) as
sys.odcinumberlist)) t1)
pivot(max(电话)
for s_id in(1 as 电话1, 2 as 电话2, 3 as 电话3, 4 as 电话4))
order by id
这个拼接语句模板drop table test;
create table test as
select 1 as ID,'张三' as 姓名,'123456;234567;789451' as 电话 from dual
union all
select 2,'李四','444444;555555;666666;777777' from dual
union all
select 3,'王五','222222;333333' from dual
测试建表create or replace procedure my_pro(p_data out sys_refcursor) is
phone_count number;
i number;
v_sql_phone varchar2(32000);
v_sql varchar2(32000);
begin
-- 获取最大的电话个数;
select max(regexp_count(t.电话, ';')) + 1 into phone_count from test t;
for i in 1 .. phone_count loop
v_sql_phone := v_sql_phone || ',' || i || ' as 电话' || i;
end loop;
v_sql_phone := ltrim(v_sql_phone, ',');
v_sql := 'select *
from (select t.id,
t.姓名,
column_value as s_id,
regexp_substr(电话, ''[^;]+'', 1, column_value) as 电话
from tmp t,
table(cast(multiset
(select level
from dual
connect by level <= regexp_count(电话, '';'') + 1) as
sys.odcinumberlist)) t1)
pivot(max(电话)
for s_id in(' || v_sql_phone || '))
order by id';
open p_data for v_sql;
end;