hive函数之行转列和列转行

行转列：将多行转为一列

1.写数据以"\t"隔开
vim shujv.txt

孙悟空  白羊座  A
老王    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

2.建表以"\t"分割

create table person_info(
   name string,
   constellation string,
   blood_type string
)
row format delimited fields terminated by '\t';

3.加载数据

load data local inpath "/opt/module/hive/shujv.txt" into table person_info;

select * from person_info;

4.查询数据

select t1.base,concat_ws("|",collect_set(t1.name)) name from 
(select name,concat(constellation, ",", blood_type) base from person_info) t1
group by t1.base;