数据拆分需求一则

在没有connect by以及拆分函数的时候（impala等），如何实现数据拆分？

数据

目标

思路

构造出一个序列，使用cross join来关联截取

with chenxu as

 (select 1 id, 'a,v,s,e,sd,ss' str

    from dual

  union all

  select 2 id, 'avs,esssd,ss' str

    from dual

  union all

  select 3 id, 'a,v,s,e,s,d,s,s' str

    from dual)

, seq as

(

select 1 seq from dual union all

select 2 from dual union all

select 3 from dual union all

select 4 from dual union all

select 5 from dual union all

select 6 from dual union all

select 7 from dual union all

select 8 from dual

)

select s.seq,t.*,

regexp_substr(t.str,'[^,]+', 1, s.seq)

from chenxu t, seq s

where s.seq between 1 and length(t.str) - length(replace(t.str, ',')) + 1

order by t.id,s.seq

that's all