需求:
id list
A 大象|狮子
B 老鹰|鸭子|企鹅|麻雀
C 乌龟|螃蟹|小龙虾|皮皮虾|鲸鱼|海豚
转成如下格式:
id name
A 大象
A 狮子
B 老鹰
B 鸭子
B 企鹅
B 麻雀
C 乌龟
C 螃蟹
C 小龙虾
C 皮皮虾
C 鲸鱼
C 海豚
实现思路:
1.建表animal_info
create table animal_info(
id string,
list array<string>)
row format delimited
fields terminated by "\t"
collection items terminated by ",";
2.使用Lateral View + explode处理数据
select t1.id,name
from animal_info t1 lateral view explode(split(list,'|')) as name
3.列转行的处理方式
select t1.id,concat_ws('|',collect_set(t1.name))
from animal_info t1
group by t1.id