oracle 逗号分割 统计

看别人帖子自己仿照写了一个

 

c1     c2      c3

01    01      a,b,c

 

转化为

 

c0    c1     c2      c3

a      01    01     a,b,c

b      01    01     a,b,c

c     01    01      a,b,c

 

 

SELECT SUBSTR(routeidlist,
              INSTR(',' || routeidlist || ',', ',', 1, t2.row_num),
              INSTR(',' || routeidlist || ',', ',', 1, t2.row_num + 1) - 1 -
              INSTR(',' || routeidlist, ',', 1, t2.row_num)) as sbid,
       t1.*
  FROM TABLE t1,
       (SELECT ROWNUM as row_num
          FROM user_objects
         where ROWNUM <= (select length(routeidlist) -
                                 length(replace(routeidlist, ',', '')) + 1
                            from TABLE
                           where TABLEID= '1000000020')) t2
 where t1.TABLEID= '1000000020'