Hive-之常用的列转行案例

该案例主要是解决将相同的column放在同一row进行展示，不同的值形成不同的列

--from 
test1.a test1.b test1.c
1       2       3
4       5       6
--to 
typ     val1    val2
a       1       4
b       2       5
c       3       6

--查询SQL，主要是将a列的所有值与'a'搭上关系，然后扁平化然后再聚合,实现思路不止一种，集思广益
select 
	typ,
	collect_list(val)[0] as val1,
	collect_list(val)[1] as val2
from(
	select 
		split(word,'-')[0] as typ,
		split(word,'-')[1] as val
	from(
		select 
			explode(array(a1,b1,c1)) as word
		from (
			select 
				concat('a-',a) as a1, 
				concat('b-',b) as b1,
				concat('c-',c) as c1
			from test1 
		) temp1
	)temp2 
) temp3 group by typ