oracle把一行拆分为多行

Question

已有课表数据，需要把ZC拆分为多行数据，其他列数据不变，如下图：

以下为拆分后的效果

Answer 1

select * from (select ag.startnum, ag.endnum,ag.zs,ag.startnum+levels.maxlev-1 as newzs, ag.xqj, ag.skdd, ag.js, ag.xq, ag.ld
  from (select substr(zs, 0, instr(zs, '-') - 1) startnum,
               substr(zs, instr(zs, '-') + 1, length(zs)) endnum,
               substr(zs, instr(zs, '-') + 1, length(zs)) -
               substr(zs, 0, instr(zs, '-') - 1) + 1 as lev,
               zs,
               xqj,
               skdd,
               js,
               xq,
               ld
          from mzs_test) ag,/*找出需要循环的数据*/
       (select rownum maxlev
          from (select max(substr(zs, instr(zs, '-') + 1, length(zs)) -
                           substr(zs, 0, instr(zs, '-') - 1) + 1) maxl
                  from mzs_test) agg
        connect by level <= agg.maxl) levels /*找出需要循环的层数*/
 where levels.maxlev <= ag.lev
 order by zs
 ) rs order by rs.zs,rs.newzs asc

=================原始数据表===================================

================查询结果表===============================================