数据(基表course):
id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e
需求:编写Hive的HQL语句来实现以下结果:表中的1表示选修,表中的0表示未选修。
id a b c d e f1 1 1 1 0 1 02 1 0 1 1 0 13 1 1 1 0 1 0
实现需求的步骤:
使用case...when...将不同的课程名称转换成不同的列。
create table student as selectid as id ,case course when a then 1 else 0 end as a,case course when b then 1 else 0 end as b,case course when c then 1 else 0 end as c,case course when d then 1 else 0 end as d,case course when e then 1 else 0 end as e,case course when f then 1 else 0 end as ffrom group by id,course;