比较好用的行转列方法

--练习题

stu_no,stu_name,course,score

1,段瑞,hive,100

1,段瑞,spark,130

1,段瑞,flink,120

1,段瑞,hadoop,100

1,段一,hive,150

1,段一,spark,140

1,段一,flink,150

1,段一,hadoop,160

1,段二,hive,210

1,段二,spark,220

1,段二,flink,240

1,段二,hadoop,220

-- 查询出如下结果

stu_no,stu_name,hive成绩,spark成绩,flink成绩,hadoop成绩

create  table tmp.lianxi(
stu_no int
,stu_name string
,course string
,score int
)
row format delimited fields terminated by ',';
load data local inpath '/root/test1' into table tmp.lianxi;

SELECT
  stu_no 
  ,stu_name
  --上面四个字段按照字典顺序排序之后的顺序就是这样
  ,split(x[0],':')[1]  as `flink成绩`
  ,split(x[1],':')[1]  as `hadoop成绩`
  ,split(x[2],':')[1]  as `hive成绩`
  ,split(x[3],':')[1]  as `spark成绩`
FROM
(
SELECT
   stu_no, 
   stu_name,
   --排序之后的成绩是按照单词的首字母进行排序的,务必按照字典顺序去取数据
   sort_array(collect_list( course || ':' || score )) as x
from tmp.lianxi
group by stu_no, stu_name) o

下面这个方法比较简单,而且可以设置查询的结果的顺序

-------------------------方法二----------------------- 

with tmp as (
select 
   stu_no, 
   stu_name,
   str_to_map(concat_ws('_',collect_list( course || ':' || score )),'_',':') as info
from tmp.lianxi
group by stu_no, stu_name
)
select 
	stu_no, 
    stu_name
	,info['hive'] as `hive成绩`	
	,info['spark'] as `spark成绩`
	,info['flink'] as `flink成绩`
	,info['hadoop'] as `hadoop成绩`
from tmp