select  
 name,
 max(case kemu when '语文' then score end) as '语文',
 max(case kemu when '数学' then score end) as '数学',
 max(case kemu when '英语' then score end) as '英语'  
from score  
group by name

------解决方案--------------------

SQL code



create table #score
(
name nvarchar(10),
 kemu nvarchar(10),
 score int
)

insert into #score
select N'小张', N'语文', 95 union all
select N'小张', N'数学', 95 union all
select N'小李', N'数学', 90 union all
select N'小李', N'英语', 90 union all
select N'小明', N'语文', 85 union all
select N'小明', N'数学', 85 union all
select N'小明', N'英语', 85 


--1,子查询法
select name,
(select MAX(score)from #score where name=s.name and kemu='语文') as 语文,
(select MAX(score)from #score where name=s.name and kemu='数学') as 数学,
(select MAX(score)from #score where name=s.name and kemu='英语') as 英语
 from #score s  group by name
 
 
 
 --2,聚合函数+case
 select  
 name,
 max(case kemu when '语文' then score end) as '语文',
 max(case kemu when '数学' then score end) as '数学',
 max(case kemu when '英语' then score end) as '英语'  
from #score  
group by name



--3,透视列法

select  name,语文,数学,英语 from
(select * from #score) as tt
pivot
(
  max(score) for kemu in(语文,数学,英语)
)
as pt