行列互转-lateral view、explode、posexplode、collect_set、collect_list

普通的行转列

--普通的行转列
select id,name from 
(select 1001 as id, 'a,b,c,d,e' as arr) a
lateral view explode(split(arr,',')) b as name
;

id	name
1001	a
1001	b
1001	c
1001	d
1001	e

带序号的行转列

--带序号的行转列
select id,rn,name from 
(select 1001 as id, 'a,b,c,d,e' as arr) a
lateral view posexplode(split(arr,',')) b as rn,name

id	rn	name
1001	0	a
1001	1	b
1001	2	c
1001	3	d
1001	4	e

列转行

--列转行
select id
,collect_set(name) as set_name  --去重、无序
,collect_list(name) as list_name  --不去重、有序
from 
(
	select 1001 as id, 'a' as name
	union all
	select 1001 as id, 'b' as name
	union all
	select 1001 as id, 'c' as name
	union all
	select 1001 as id, 'd' as name
	union all
	select 1001 as id, 'e' as name
) a
group by id

id	set_name	list_name
1001	["c","b","a","e","d"]	["a","b","c","d","e"]