with tb as (
    select 
        name,
        subject,
        score
    from values('兮辰','语文',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
group by name;
--结果如下：
name chi math eng   pe
兮辰  85  92  98  91
无尽  90  89  93  86

--该方式也有弊端，必须保障原表各科目顺序是一致的，否则从数组里拿出来的成绩将不对应
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 
    group by name
)tmp;
--结果如下：
name    chi math    eng pe
兮辰  85  92  98  91
无尽  90  89  93  86

--将字符串'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
    group by name
)tmp
--结果如下：
name    chi math    eng pe
兮辰  85  92  98  91
无尽  90  89  93  86

with tb as (
    select 
        name,
        Chi,
        Math,
        Eng,
        PE
    from values ('兮辰',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