有张表分成八种情况，分别为：
账面库存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