一个字符串分隔行转列的问题

[翼帆]

论坛肯定有，一时找不到，望大神解答。

表A有如下数据

col1 col2
1 a,d
2 a,aad,sd

期望查询结果

col1 col2
1 a
1 d
2 a
2 aad
2 sd

[yixilan]

with a as
(select 1 as col1, 'a,d' as col2 from dual
union all
select 2 as col1, 'a,aad,sd' as col2 from dual)
select distinct sub.col1, sub.col2 from
(select col1, regexp_substr(col2, '\w+', 1, level) col2 from a
connect by level < 10) sub
where sub.col2 is not null
order by sub.col1;

[horizonlyhw]

with t as
(
select 1 col1,'a,d' col2 from dual
union all
select 2 col1,'a,aad,sd' col2 from dual
)

select distinct t.col1,regexp_substr(t.col2,'[^,]+',1,level)
from t
connect by level<=length(regexp_replace(t.col2,'[^,]+'))+1

order by 1,2

[翼帆]

感谢 @yixilan @horizonlyhw
用正则效率很慢，比如几十万甚至更多数据。能否有其它替代方案

[xpingping]

A 表tb1 tb2是输出物表……

create table tb1
(col1 varchar2(4),
col2 varchar2(50)
)
create table tb2
(col1 varchar2(4),
col2 varchar2(50)
)

create or replace procedure p_insert is
	cursor cur_tb1 is
		select * from tb1;
	row_cur_tb1 cur_tb1%rowtype;
begin
	execute immediate 'truncate table tb2';
	for row_cur_tb1 in cur_tb1
	loop
		insert into tb2
			select row_cur_tb1.col1,
				   regexp_substr(row_cur_tb1.col2, '[^,]+', 1, rownum)
			  from dual
			connect by rownum <= length(regexp_replace(row_cur_tb1.col2, '[^,]+')) + 1;
	end loop;
	commit;
exception
	when others then
		dbms_output.put_line('error');
		rollback;
end p_insert;

[minitoy]

呵呵，问题很有意思，一般是反着问的。
我有1 a 1 b 1 c
如何得到 1 a,b,c

[samfeng_2003]

--创建表
create table tb1
(col1 varchar2(4),
col2 varchar2(50)
);
--插入数据
insert into tb1
select '1','a,d' from dual
union all
select '2','a,aad,sd' from dual;
Commit;
--执行查询
select t1.col1,substr(t1.col2, t2.id, instr(t1.col2||',',',',t2.id)-t2.id) col2
  from tb1  t1,
         (select rownum id from dual connect by rownum<(select max(length(col2)) from tb1)) t2
where t2.id<=length(t1.col2)
          and instr(','||t1.col2,',',t2.id)=t2.id
            order by t1.col1,t2.id;
--删除表
drop table tb1 cascade constraints;

COL1 COL2                                              
---- --------------------------------------------------
1    a                                                 
1    d                                                 
2    a                                                 
2    aad                                               
2    sd                                                

5 rows selected.