# ##[函数]Hive中行列转换(行转列)

Hive中行列转换 – 菠萝大数据梦工厂（Free World） – 博客频道 – CSDN.NET http://blog.csdn.net/jiangshouzhuang/article/details/46810529

1、演示多列转为单行

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

create table student(name string,subject string,score decimal(4,1))
row format delimited
fields terminated by ‘|’;

hive (hive)> select name,concat_ws(‘,’,collect_set(subject)) from student group by name;
huahua chinese,math
xiaoming english,chinese,math

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、演示单行转为多列

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

create table student2(name string,subject_score_list string)
row format delimited
fields terminated by ‘|’;

hive (hive)> select * from student2;
student2.name student2.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

原文作者：葡萄喃喃呓语
原文地址: https://www.jianshu.com/p/6761a37740eb
本文转自网络文章，转载此文章仅为分享知识，如有侵权，请联系博主进行删除。