使用 group by 和 case when

declare @t as table
(
 FName   varchar(10),
 FCourse varchar(10),
 FScore  decimal(4,1)
)

insert into @t(FName,FCourse,FScore)
values
('3-张三','语文',95),
('3-张三','数学',80),
('3-张三','英语',70),

('4-李四','语文',88),
('4-李四','数学',87),
('4-李四','英语',86),

('5-王五','语文',90),
('5-王五','数学',80),
('5-王五','英语',70)

select * from @t

 

select 
 FName ,
 max(case FCourse when '语文' then FScore else 0 end) as '语文',
 max(case FCourse when '数学' then FScore else 0 end) as '数学',
 max(case FCourse when '英语' then FScore else 0 end) as '英语'
from @t
group by FName

 

select 
 FName,
 case 
  when FCourse='数学' then '理科' 
  else '文科' 
 end as '科别',
 sum(FScore) as '总分'
from @t
group by FName,case when FCourse='数学' then '理科' else '文科' end 
order by FName

 

select 
 FName,
 FType as '科别',
 sum(FScore) as '总分' 
from (
 select 
  FName, FCourse, FScore,
  FType = case when FCourse in ('语文','英语') then '文科' else '理科' end 
 from @t
) as a
group by FName,FType
order by FName