hive sql练习多行变多列

描述：表中记录了各年份各部门的平均绩效考核成绩
表结构：
a -- 年份
b -- 部门
c -- 绩效得分

表内容
t1.a    t1.b    t1.c
2014    B       9
2015    A       8
2014    A       10
2015    B       7

建表语句

create table t25(
  a string,
  b string,
  c int
)row format delimited
fields terminated by ',';
load data local inpath '/home/hivedata/t25.txt' into table t25;

with t1 as (
    select distinct a,concat_ws('',collect_set(concat(b,c)) over (partition by c)) temp from t25
    ),t2 as (select distinct a,
                     `if`(substr(temp,1,1)='A',substr(temp,2),null ) as col_A,
                     `if`(substr(temp,1,1)='B',substr(temp,2),null ) as col_B
                     from t1) select a,
                                     sum(case when col_A is not null then col_A end ) col_A,
                                     sum(case when col_B is not null then col_B end ) col_B
                                     from t2 group by a;