create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
Insert tb
Select '张三','语文',60 union all
Select '张三','数学',70 union all
Select '张三','英语',80 union all
Select '张三','物理',90 union all
Select '李四','语文',65 union all
Select '李四','数学',75 union all
Select '李四','英语',85 union all
Select '李四','物理',95
go
select [姓名]=max([姓名]), 数学=sum(case when [课程]='数学' then [分数] else 0 end),
物理=sum(case when [课程]='物理' then [分数] else 0 end),英语=sum(case when [课程]='英语'
then [分数] else 0 end),语文=sum(case when [课程]='语文' then [分数] else 0 end) from tb group by [姓名]