--name    subject    score
with cte as 
(
    select 'tom'  as name,'语文' as [subject],88 as score union all
    select 'tom','数学',97 union all
    select 'tom','英语',78 union all
    select 'abama','语文',76 union all
    select 'abama','数学',92 union all
    select 'abama','英语',100 
)
select 
    name,
    SUM(case [subject] when '语文' then score else 0 end) as 语文, 
    SUM(case [subject] when '数学' then score else 0 end) as 数学,
    SUM(case [subject] when '英语' then score else 0 end) as 英语
from cte
group by name
/*数据多的话，就用动态脚本 行转列*/

/*
name  语文          数学          英语
----- ----------- ----------- -----------
abama 76          92          100
tom   88          97          78

(2 row(s) affected)
*/