oracle的Unpivot转hive语句写法 行转列

针对Oracle数据库的行列转换成hive语句，整理一篇文档：
首先创建一版测试数据

create table ods.student_test as 
select '张三' name,65 chinese,77 math,65 english,85 physics,292 total from dual
union all
select '李四' name,78 chinese,87 math,82 english,90 physics,337 total from dual

oracle的原始写法和展示结果：

select name,科目,成绩 from ods.student_test  unpivot (成绩 for 科目 in (chinese, math, english, physics));

hive写法

select t1.name `名称`,t2.`科目`,t2.`成绩` 
from dw.student_test t1
LATERAL VIEW explode (map(
  '语文',t1.chinese,
  '英语', t1.english,
  '数学', t1.math,
  '物理', t1.physics
)) t2 as `科目`, `成绩`

求得各分科成绩
oracle写法

select  * from ods.student_test 
unpivot (
(科目1成绩,科目2成绩) for 科目 in ((chinese,english) as '文科',(math,physics) as '理科')
);

hive写法

select t1.name `名称`,t2.`科目`,t2.value[0] `科目1成绩`,t2.value[1] `科目2成绩` from 
dw.student_test t1
LATERAL VIEW explode (map(
  '文科', array(t1.chinese,t1.english),
  '理科', array(t1.math,t1.physics)
)) t2 as `科目`, value