impala根据某字段一行转多行

Question

在impala环境下，想将红框左表的数据一列转多行变成右表，请问该怎么转呢？

用iql

Answer 1

impala没用过，我这边给出MySQL的解决方案

create table t1(banji varchar(20), students varchar(20));

insert into t1 values ('1班','张三,李四,王五'),('2班','赵六,陈一');



select  banji,
        SUBSTRING_INDEX(SUBSTRING_INDEX(students,',',tmp1.rn),',',-1) as new_str2
from t1
inner join 
(select 1 as rn
 union 
 select 2 as rn
 union 
 select 3 as rn
) tmp1
on length(t1.students) - length(replace(t1.students,',','')) + 1 >= tmp1.rn
order by t1.banji
;