简单查询sql,看下大家有其他方法没！

Question

我有oracle数据库中有这样一个表，有如下三列 
claimno        checkdate 
0001001  17,15,6  2009-9-10 
我想将其拆分成如下三行： 
claimno        checkdate 
0001001    17      2009-9-10 
0001001    15      2009-9-10 
0001001    6        2009-9-10 

Answer 1

用sql解决！值格式是不固定的！但都是以，分隔的数字！

Answer 2

select claimno,,checkdate
from
(
select claimno,
substr
(','||||',',
instr(','||||',',',',1,lvl)+1,
instr(','||||',',',',1,lvl+1)-instr(','||||',',',',1,lvl)-1
) ,
checkdate
from
(
select * from tb,
(
select level lvl from dual connect by level <= (select max(length()-length(replace(,','))+1) from tb)
) t1
order by claimno,lvl
)
)
where  is not null

Answer 3

SQL> select * from tb;
 
CLAIMNO              MARK                                     CHECKDATE
-------------------- ---------------------------------------- -----------
001001               17,15,6                                  2010-6-4 16
001002               18,7                                     2010-6-4 16
SQL> select claimno,,checkdate
  2  from
  3  (
  4  select claimno,
  5  substr
  6  (','||||',',
  7  instr(','||||',',',',1,lvl)+1,
  8  instr(','||||',',',',1,lvl+1)-instr(','||||',',',',1,lvl)-1
  9  ) ,
 10  checkdate
 11  from
 12  (
 13  select * from tb,
 14  (
 15  select level lvl from dual connect by level <= (select max(length()-length(replace(,','))+1) from tb)
 16  ) t1
 17  order by claimno,lvl
 18  )
 19  )
 20  where  is not null
 21  ;
 
CLAIMNO              MARK                                                                             CHECKDATE
-------------------- -------------------------------------------------------------------------------- -----------
001001               17                                                                               2010-6-4 16
001001               15                                                                               2010-6-4 16
001001               6                                                                                2010-6-4 16
001002               18                                                                               2010-6-4 16
001002               7                                                                                2010-6-4 16
 

Answer 4

MARK一下，这个不难，下周一来实现