行变列

已知：数据库
name       lesson       score
张三       语文           90
李四       数学           94
王五       英语           87
张三       数学           77
李四       语文           80
王五       数学           68

 

create table scores 
(
    name nvarchar(50) not null,
    lesson nvarchar(20) not null,
    score int not null
)

 

select name as 姓名,
(select score from scores where name=c.name and lesson='语文' ) as 语文,
(select score from scores where name=c.name and lesson='数学') as 数学,
(select score from scores where name=c.name and lesson='英语') as 英语,
max(score) as 最高分,
avg(score) as 平均分,
sum(score) as 总分
 from scores as c group by name having avg(score)>=73