oracle根据分隔符将一行拆分为多行

--oracle根据分隔符将一行拆分为多行
with tmp as --临时数据集
 (select 1 id, 'one,two,three' names
    from dual
  union all
  select 2 id, 'four,five,six' names
    from dual
  union all
  select 3 id, 'seven,eight,nine' names
    from dual
  union all
  select 4 id, 'ten,eleven' names
    from dual)
select t1.id, regexp_substr(t1.names, '[^,]+', 1, t2.lv)--截取对应行数的数据
  from tmp t1,
       (select level lv--生成行数序列数据 1到最大行数
          from (select max(regexp_count(a.names, '[^,]+', 1)) r_count--计算数据集中拆分后最大的行数
                  from tmp a) b
        connect by level <= b.r_count) t2
 where regexp_substr(t1.names, '[^,]+', 1, t2.lv) is not null-- 排除掉空的数据
 order by 1;