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 中，已经有实现此功能的内置方法了。