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

1、多行行转列和列转行

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

列转行：union all

例如：

转为

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

将第一张表转为下表

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

如将上表转换成

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

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