原表数据如下:
create table test_20170523 as
select 1 id,'abc' name from dual
union all
select 2 id,'de' name from dual
union all
select 3 id,'fg' name from dual;
ID NAME
1 abc
2 de
3 fg
需求如下:
ID NAME
1 a
1 b
1 c
2 d
2 e
3 f
3 g
可以使用Oracle的LEVEL来控制截取,代码如下:
select distinct id,str name from (
select id,substr(str, LEVEL, 1) STR
from (select id,name str from test_20170523)
CONNECT BY LEVEL <=(select max(length(name)) from test_20170523)) where str is not null order by id,str;
LEVEL 的大小由test_20170523的name字段最大长度来控制,结果即为需求输出。