MaxCompute 行转列 列转行

发现MaxCompute相关的帖子真不多，补充发布一个。实际上，实现行转列和列转行的方法，根据实际需求应该还有很多种方法。MaxCompute其实也提供了很多的函数来帮助大家实现，有机会可以多探索几种方式。
一、行转列

with ta as (
select * from values
 ('张三' , '语文' , 74)
,('张三' , '数学' , 83)
,('张三' , '物理' , 93)
,('李四' , '语文' , 74)
,('李四' , '数学' , 84)
,('李四' , '物理' , 94)
t(name , subject , result))

--方法一：使用case when end结构,通用写法
select name as 姓名
,max(case subject when '语文' then result end) as 语文
,max(case subject when '数学' then result end) as 数学
,max(case subject when '物理' then result end) as 物理
  from ta
 group by name
;
+--------+------------+------------+------------+
| 姓名 | 语文     | 数学     | 物理     |
+--------+------------+------------+------------+
| 张三 | 74         | 83         | 93         |
| 李四 | 74         | 84         | 94         |
+--------+------------+------------+------------+
--方法二：非要多写一步的写法
with ta as (
select * from values
 ('张三' , '语文' , 74)
,('张三' , '数学' , 83)
,('张三' , '物理' , 93)
,('李四' , '语文' , 74)
,('李四' , '数学' , 84)
,('李四' , '物理' , 94)
t(name , subject , result))
select name
      ,keyvalue(subject,'语文') as 语文
      ,keyvalue(subject,'数学') as 数学
      ,keyvalue(subject,'物理') as 物理
  from(
select name,wm_concat(';',concat(subject,':',result))as subject
  from ta
 group by name)tt
;

这个方法用wm_concat做聚合之前，先用concat把多列合并为一列。然后在拼接后用keyvalue去解析出来。
二、列转行

with tb as (
select * from values
 ('张三', 88,99,89)
,('李四', 78,77,87)
t(name , subject_yw, subject_sx, subject_wl))
--方法一：使用union all,通用写法
select name,subject,result
  from(
select name,'语文' as subject,subject_yw as result from tb
 union all
select name,'数学' as subject,subject_sx as result from tb
 union all
select name,'物理' as subject,subject_wl as result from tb)tt;
+------+---------+--------+
| name | subject | result |
+------+---------+--------+
| 张三 | 语文  | 88     |
| 张三 | 数学  | 99     |
| 张三 | 物理  | 89     |
| 李四 | 语文  | 78     |
| 李四 | 数学  | 77     |
| 李四 | 物理  | 87     |
+------+---------+--------+
with tb as (
select * from values
 ('张三', 88,99,89)
,('李四', 78,77,87)
t(name , subject_yw, subject_sx, subject_wl))
--方法二：
select name,split_part(subject,':',1) as subject
           ,split_part(subject,':',2) as result
  from(
select trans_array(1,';',name,subject) as (name,subject) 
  from(
select name
,concat('语文',':',subject_yw
   ,';','数学',':',subject_sx
   ,';','物理',':',subject_wl) as subject
  from tb)tt)tx;

这个例子，为了与通用方式产出一致。用到了 trans_array() 函数,split_part()函数，为了构造结构，还用concat拼接了字符串。

上面这两个针对maxcompute的例子，实际上看上去并不会比通用方法效率高，只是提供了一种思路。大家实际使用中有类似的结构，可以参考借鉴。