Oracle中一个列转行例子的几种解法
已知数据格式如下:
a 1,2,3
b 4,5,6
希望实现效果如下:
a 1
a 2
a 3
b 4
b 5
b 6
--创建测试表和添加测试数据
create table test( a1 varchar2(10),b1 varchar2(10));
insert into test(a1,b1) values('a','1,2,3');
insert into test(a1,b1) values('b','4,5,6');
commit;
--方法1
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <= length(b1)-length(replace(b1,','))+1
--方法2
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <=length(regexp_replace(b1,'[^,]+')) +1
--方法3
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
已知数据格式如下:
a 1,2,3
b 4,5,6
希望实现效果如下:
a 1
a 2
a 3
b 4
b 5
b 6
--创建测试表和添加测试数据
create table test( a1 varchar2(10),b1 varchar2(10));
insert into test(a1,b1) values('a','1,2,3');
insert into test(a1,b1) values('b','4,5,6');
commit;
--方法1
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <= length(b1)-length(replace(b1,','))+1
--方法2
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <=length(regexp_replace(b1,'[^,]+')) +1
--方法3
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <=regexp_count(b1,',')+1