if(EXISTS( SELECT * FROM sysobjects
WHERE ID = OBJECT_ID('student')))
drop table student
create table student
(
stuid char(10) not null,
stuname varchar(50) not null
)
insert into student values('050614001','张三')
insert into student values('050614002','李四')
insert into student values('050614003','王二')
insert into student values('050614004','黑六')
if(EXISTS( SELECT * FROM sysobjects
WHERE ID = OBJECT_ID('subject')))
drop table subject
create table subject
(
sid int not null,
sname varchar(50) not null
)
insert into subject values(1,'语文')
insert into subject values(2,'数学')
insert into subject values(3,'英语')
insert into subject values(4,'物理')
insert into subject values(5,'化学')
if(EXISTS( SELECT * FROM sysobjects
WHERE ID = OBJECT_ID('score')))
drop table score
create table score
(
stuid char(10) not null,
sid int not null,
score int not null
)
insert into score values('050614001',1,87)
insert into score values('050614001',2,89)
insert into score values('050614001',3,90)
insert into score values('050614002',5,70)
insert into score values('050614002',1,91)
insert into score values('050614002',2,92)
insert into score values('050614004',3,93)
insert into score values('050614003',1,94)
insert into score values('050614003',2,95)
insert into score values('050614003',3,86)
declare @strSQL varchar(8000)
SET @strSQL = 'select student.stuName [姓名]'
select @strSQL = @strSQL + ',SUM(CASE subject.SNAME WHEN ''' + SNAME + ''' THEN score.[Score] END) [' + SNAME + ']' FROM (SELECT SNAME FROM [Subject]) AS tmp
set @strSQL = @strSQL + ',sum(score) as 总分 from student
left join score on student.stuId = score.stuid
left join subject on score.sid = subject.sid
GROUP BY student.STUID, student.STUNAME
order by 总分 desc'
exec (@strSQl)
SQL SERVER 2005 中，已经有实现此功能的内置方法了。