# Hive中行列转换

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

Impala的行列转换请查看： http://blog.csdn.net/jiangshouzhuang/article/details/46809931

#### HIVE行转列和列转行

2018-01-28 21:54:26

#### hive之实现列转行

2015-06-20 16:50:00

#### hive转置

2015-08-21 08:14:43

#### hive collect_set,lateral view,explode 实现行列转换

2017-05-23 11:50:28

#### hive-列转行和行转列

2014-08-26 21:27:51

#### hive行列转换总结

2013-10-12 16:22:22

#### Hive行列转换-lateral view的使用

2017-05-19 14:22:08

#### hive的行列转换

2018-06-28 16:45:11

#### hive行列转换面试题

2018-03-25 17:39:11

#### Hive行列转换

2018-04-14 20:12:38