行转列面试题

 

 在hive里建表

在hive里一条一条的插入数据

insert into table StudentSc values('张三','语文',74);
insert into table StudentSc values('张三','数学',83);
insert into table StudentSc values('张三','物理',93);

insert into table StudentSc values('李四','语文',74);
insert into table StudentSc values('李四','数学',84);
insert into table StudentSc values('李四','物理',94);

编写的sql语句

SELECT
UserName,
MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS `语文`,
MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS `数学`,
MAX(CASE Subject WHEN '物理' THEN Score ELSE 0 END) AS `物理`
FROM StudentSc
GROUP BY UserName;

得到的答案