老问题：Oracle行转列（字符串拆分）

Question

现有以下数据：
ID  Name
1   Jack,Tom,Ben
2   Mary,Simth,Tony,Jay
转换为：
ID  Name
1   Jack
1   Tom
1   Ben
2   Mary
2   Simth
2   Tony
2   Jay要求使用SQL查询完成，由于条件限制，不能使用函数，过程，游标之类的方法。
请大虾们帮忙吧！

Answer 1

with tt as (select 1 id,'Jack,Tom,Ben' Name from dual
  union all select 2,'Mary,Smith,Tony,Jay' from dual)
  
select a.id,
  substr(','||a.name||',',instr(','||a.name,',',1,b.rn)+1,
    instr(a.name||',',',',1,b.rn)-instr(','||a.name,',',1,b.rn))name
from tt a,
(select rownum rn from dual
  connect by rownum<10)b
where length(a.name)-length(replace(a.name,','))+1>=b.rn
order by id,b.rn
这个10要按你的实际情况修改
不小于原表中name里项数的最大值

Answer 2

如果是10g的话可以
with tt as (
select 1 id,'Jack,Tom,Ben' Name from dual
union all select 2,'Mary,Smith,Tony,Jay' from dual
)
select substr(name,
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1),
              instr(name, ',', 1, rownum) -
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1)) 
  from (select wmsys.wm_concat(name) name from tt) a
connect by rownum <= 5 and instr(name, ',', 1, rownum)>0

Answer 3

with tt as ( 
select 1 id,'Jack,Tom,Ben' Name from dual 
union all select 2,'Mary,Smith,Tony,Jay' from dual 
) 
select substr(name, 
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1), 
              instr(name, ',', 1, rownum) - 
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1)) 
  from (select wmsys.wm_concat(name)||',' name from tt) a 
connect by rownum <= 5 and instr(name, ',', 1, rownum)>0 

Answer 4

with tt as ( 
select 1 id,'Jack,Tom,Ben' Name from dual 
union all select 2,'Mary,Smith,Tony,Jay' from dual 
) 
select substr(name, 
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1), 
              instr(name, ',', 1, rownum) - 
              decode(rownum, 1, 1, instr(name, ',', 1, rownum - 1)+1)) 
  from (select wmsys.wm_concat(name)||',' name from tt) a 
connect by instr(name, ',', 1, rownum)>0

Answer 5

create table test(id varchar2(10),mc varchar2(50));insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222'); 
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;-----------------------------------
select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from 
(
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test )
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;