Hive列转行总结

有张表分成八种情况，分别为：

账面库存X类
标准库存X类
待核销库存X类
待冲销库存X类
账面库存Y类
大众标准库存Y类
待核销库存Y类
待冲销库存Y类

于是考虑先使用拼接函数生成如下样式：

select concat(nvl(type1,''),nvl(type2,''),nvl(type3,''),nvl(type4,''),nvl(type5,'')) from student_test 

建立测试表：

CREATE table student_test(
	stu_id string comment '学号',
	stu_name string comment '姓名',
	stu_type string comment '类型',
	courses string comment '各个科目',
	scores string comment '各个分数'
) comment '学生成绩表';

insert into student_test values 
("1001", "张三","type1,type2,type3,type4,type5","语文,数学,英语,历史,地理", "88,87,94,86,84"),
("1002", "李四","type1,type2,type3,type4,type5", "语文,数学,英语,历史,地理", "78,89,75,79,68"),
("1003", "王五","type1,type2,type3,type4,type5", "语文,数学,英语,历史,地理", "98,97,91,93,92"),
("1004", "朱六","type1,type2,type3,type4,type5", "语文,数学,英语,历史,地理", "66,63,64,67,68");

具体测试代码如下：

select stu_id, stu_name,stu_types, course, score 
from student_test 
lateral view posexplode(split(stu_type, ',')) table_view1 as a, stu_types 
lateral view posexplode(split(courses, ',')) table_view1 as b, course 
lateral view posexplode(split(scores, ',')) table_view2 as c, score 
where a=b and b=c;

方法二：
不拼接，直接把八种情况写入到一行数据中，然后group by+union all（每组字段个数必须相同）

select  
  stu_id
 , tu_name
 ,'type1'   as type
​ ,course1   as course
​ ,score1   as score
​from  test_table
​group by stu_id, stu_name​,course1,score1
union all
select  
  stu_id
 , tu_name
 ,'type2'   as type
​ ,course2   as course
​ ,score2   as score
​from  test_table
​group by stu_id, stu_name​,course2,score2
union all
select  
  stu_id
 , tu_name
 ,'type3'      as type
​ ,course3   as course
​ ,score3   as score
​from  test_table
​group by stu_id, stu_name​,course3,score3
union all
select  
  stu_id
 ,stu_name
 ,'type4'   as type
​ ,course4   as course
​ ,score4   as score
​from  test_table
​group by stu_id, stu_name​,course4,score4
union all
select  
  stu_id
 ,stu_name
 ,'type5'   as type
​ ,course5   as course
​ ,score5   as score
​from  test_table
​group by stu_id, stu_name​,course5,score5