hive中行转列，列转行的各种情况及解决方法

1、多行行转列和列转行

行转列：一般用case when,再做一个group by 去掉0值。

列转行：union all

例如：

name	year	sa
xiaohong	2000	1000
dahei	2001	2000
dahei	2000	1000
dahei	2001	2000

转为

name	year1	year2
xiaohong	1000	2000
dahei	1000	2000

select name,
sum(case year when '2001' then sa else 0 end) year1,
sum(case year when '2002' then sa else 0 end) year2,
from a group by name;

反过来则为

select name,'2000' as year,year1 as sa from a
union all
select name,'2001' as year,year2 as sa from a

2、多行转一列，concat_ws，结合collect_set|collect_list

将第一张表转为下表

name	detail
dahei	2000:1000,2001:2000

select name,
concat_ws(',',collect_set(concat(year,':',cast(sa as string)))) as detail
from a group by name;

3、一列转多列，如把上表分开，可用split,substring等字符串处理函数。

4、一行转多行，lateral view 子窗口结合 explode

如将上表转换成

name	detail
dahei	2000:1000
dahei	2001:2000

select name,detail from a lateral view explode(split(detail,'\\,')) a as detail;

如果应用多行 lateral view explode则做笛卡尔积。