SQL SERVER 交叉报表

if exists (select *  from sysdatabases where name='test')
drop database test
go
create database test
on
(
name='test.mdf',
filename='E:/DataBase/test.mdf',
size=4
)
log on
(
name='test.ldf',
filename='E:/DataBase/test_log.ldf',
size=4
)
go
if exists (select *  from sysobjects where name='student')
drop table student
go
create table student
(
SId int identity(1,1) primary key,
Sname varchar(20) not null
)
go
insert into student values ('郑绪栋')
insert into student values ('zhengxudong')
insert into student values ('AndyLau')
insert into student values ('绪栋')
select *  from student
if exists (select *  from sysobjects where name='Course')
drop table Course
go
create table Course
(
CId int identity(1,1) primary key,
Cname varchar(20) not null
)
go
insert into Course values ('语文')
insert into Course values ('数学')
insert into Course values ('英语')
insert into Course values ('音乐')
select *  from Course
if exists (select *  from sysobjects where name='Score')
drop table Score
go
create table Score
(
Id int identity(1,1) primary key,
Scid int not null,
Ssid int not null,
Score int not null,
constraint Fk_Score_Course_id foreign key (Scid) references Course (CId),
constraint Fk_Score_Student_id foreign key (Ssid) references Student (SId)
)
go
insert into Score values (1,1,80)
insert into Score values (1,2,83)
insert into Score values (1,3,75)
insert into Score values (1,4,90)

insert into Score values (3,1,70)
insert into Score values (2,2,80)
insert into Score values (3,3,75)
insert into Score values (2,4,90)

insert into Score values (2,1,65)
insert into Score values (2,2,65)
insert into Score values (3,3,75)
insert into Score values (3,4,85)

insert into Score values (4,1,88)
insert into Score values (4,2,80)
insert into Score values (4,3,75)
insert into Score values (4,4,95)

select *  from Score
select *  from Course
select *  from student

--查询出各位同学各门功课的成绩
select (select s.Sname from student s where s.SId=Score.Ssid ) 姓名,
sum(case scid when 1 then score end) 语文,
sum(case scid when 2 then score end) 数学,
sum(case scid when 3 then score end) 英语,
sum(case scid when 4 then score end) 音乐
 from score group by ssid