现有以下数据：
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查询完成，由于条件限制，不能使用函数，过程，游标之类的方法。
请大虾们帮忙吧！
SQL code:
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里项数的最大值
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, rownu
oracle连接啥这么复杂啊.
oracle 10g
用ps/sql dev怎么也连不上.
用sqlplus可以登陆.net manager之前测试连接了成功的.现在搞得也连接不了.
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
......
