# 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
;

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;

+ 订阅