hive里通常通过collect_set和collect_list来进行列转行,其中collect_list为不去重转换,collect_set为去重转换。
创建一个测试表
CREATE table stu_score(
stu_id string comment '学号',
stu_name string comment '姓名',
course string comment '科目',
score string comment '分数'
) comment '学生成绩表';
数据如下:
列转行实现:
select
stu_id,
stu_name,
concat_ws(’,’,collect_set(course)) as course,
concat_ws(’,’,collect_set(score)) as score
from stu_score
group by stu_id, stu_name;
结果: