MaxCompute SQL使用小技巧之行列转换

with tb as(select        name,        subject,        score
fromvalues('兮辰','语文',85),('兮辰','数学',92),('兮辰','英语',98),('兮辰','体育',91),('无尽','语文',90),('无尽','数学',89),('无尽','英语',93),('无尽','体育',86)               t(name,subject,score))

select    name,    max(case when subject ='语文' then score end)as Chi,    max(case when subject ='数学' then score end)as Math,    max(case when subject ='英语' then score end)as Eng,    max(case when subject ='体育' then score end)as PE
from tb
groupby name;--结果如下：name chi math eng pe
兮辰  85929891无尽  90899386

--该方式也有弊端，必须保障原表各科目顺序是一致的，否则从数组里拿出来的成绩将不对应select    name,    score_list[0]as Chi,    score_list[1]as Math,    score_list[2]as Eng,    score_list[3]as PE
from(select        name,        collect_list(score)as score_list
from tb 
groupby name
)tmp;--结果如下：name  chi math  eng pe
兮辰  85929891无尽  90899386

--将字符串'1:a;2:b'拆分为Key-Value对，返回其中key为1的value值select keyvalue('1:a;2:b',1);--a

select    name,    keyvalue(subject,'语文')as Chi,    keyvalue(subject,'数学')as Math,    keyvalue(subject,'英语')as Eng,    keyvalue(subject,'体育')as PE
from(select        name,        wm_concat(';',concat(subject,':',score))as subject
from ta
groupby name
)tmp
--结果如下：name  chi math  eng pe
兮辰  85929891无尽  90899386

with tb as(select        name,        Chi,        Math,        Eng,        PE
fromvalues('兮辰',85,92,98,91),('无尽',90,89,93,86)                t(name,Chi,Math,Eng,PE))

select    name,    subject,    score
from(select name,'语文'as subject,Chi as score from tb
union all 
select name,'数学'as subject,Math as score from tb
union all 
select name,'英语'as subject,Eng as score from tb
union all 
select name,'体育'as subject,PE as score from tb
);--结果如下：name subject  score
兮辰  语文  85无尽  语文  90兮辰  数学  92无尽  数学  89兮辰  英语  98无尽  英语  93兮辰  体育  91无尽  体育  86

select    name,    subject,    score
from(select            name,            map('语文',Chi,'数学',Math,'英语',Eng,'体育',PE
)as kv
from tb
) tmp 
lateral view explode(kv) t as subject,score;--结果如下：name subject  score
兮辰  体育  91兮辰  数学  92兮辰  英语  98兮辰  语文  85无尽  体育  86无尽  数学  89无尽  英语  93无尽  语文  90

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('语文',':',Chi,';','数学',':',Math,';','英语',':',Eng,';','体育',':',PE)as subject
from tb
)tmp1
)tmp2;--结果如下：name  subject result
兮辰  语文  85兮辰  数学  92兮辰  英语  98兮辰  体育  91无尽  语文  90无尽  数学  89无尽  英语  93无尽  体育  86