hive行列转换

1.行专列

表数据:

name ,subject ,score

xiaoming|english|92.0
xiaoming|chinese|98.0
xiaoming|math|89.5
huahua|chinese|80.0
huahua|math|89.5

hive (hive)> select name,concat_ws(',',collect_set(concat(subject,'=',score))) from student group by name;
huahua chinese=80,math=89.5
xiaoming english=92,chinese=98,math=89.5


2、列转行

表数据

name ,subject_score_list

huahua|chinese=80,math=89.5
xiaoming|english=92,chinese=98,math=89.5

hive (hive)> select name, subject_list from student2 stu2 lateral view explode(split(stu2.subject_score_list,','))stu_subj as subject_list;

huahua chinese=80
huahua math=89.5
xiaoming english=92
xiaoming chinese=98
xiaoming math=89.5