Hive行转列

源数据
data              title      uv
2019-01-01	docter	100
2019-01-01	nurse	99
2019-01-02	nurse	199
2019-01-02	docter	200

输出数据

data       docter nurse	
2019-01-01	100	99
2019-01-02	200	199

在hive中创建表

create table test01(data string,title string,uv int) 
row format delimited fields terminated by '\t';

load数据

load data local inpath "/root/test01.txt" into table test01

查询语句为

select data
,concat_ws('',collect_list(docter))     docter
,concat_ws('',collect_list(nurse))   nurse
from
(  select data
,case when title='docter' then uv else '' end as  docter
,case when title='nurse' then uv else '' end as  nurse  
from test01
) a 
group by data; 

统计结果