hive sql练习1：行转列，列转行问题

习题描述

表名：t1（源表）

表结构
a -- 年份
b -- 部门
c -- 绩效得分

表内容

+--------+--------+--------+--+
|   a    |    b   |    c   |
+--------+--------+--------+--+
| 2014   | B      | 9      |
| 2015   | A      | 8      |
| 2014   | A      | 10     |
| 2015   | B      | 7      |
+--------+--------+--------+--+

问题1

请用 SQL 实现将上述表内容转为如下输出结果所示：

+-------+--------+--------+--+
|   a   | col_A  | col_B  |
+-------+--------+--------+--+
| 2014  | 10     | 9      |
| 2015  | 8      | 7      |
+-------+--------+--------+--+

问题2

请用 SQL 实现将上述问题的结果转成源表，上述结果表名为 t1_2。

实现

问题1

with t1 as(
select 2014 as a, 'B' as b, 9 as c union all 
select 2015 as a, 'A' as b, 8 as c union all 
select 2014 as a, 'A' as b, 10 as c union all 
select 2015 as a, 'B' as b, 7 as c 
)
select 
a,
--聚合函数实现，max，sum都可
max(case when b = 'A' then c end) as col_A,
max(case when b = 'B' then c end) as col_B
from t1
group by a;

行转列问题。

问题2

with t1_2 as(
select 2014 as a, 10 as col_A, 9 as col_B union all 
select 2015 as a, 8 as col_A, 7 as col_B
)
select a, 'A' as b, col_A as c from t1_2
union all 
select a, 'B' as b, col_B as c from t1_2
;

列转行问题。