use tempdb;
go
--科目表
if object_id('dbo.subject') is not null
drop table dbo.subject;
go
create table subject(sid int identity(1,1) not null primary key ,sname varchar(1000));
go
--学生表
if object_id('dbo.student') is not null
drop table dbo.student
go
create table dbo.student(stuid char(10) not null primary key, studname varchar(50))
go
if object_id('dbo.score') is not null
drop table dbo.score
go
create table dbo.score(stuid char(10) not null,sid int not null,
score int not null,
constraint pk_score(stuid,sid));
insert into subject(sname) values('语文');
insert into subject(sname) values('数学');
insert into subject(sname) values('英语');
DECLARE @strSQL VARCHAR(8000)
SET @strSQL = 'SELECT t.STUNAME [姓名]'
SELECT @strSQL = @strSQL + ',SUM(CASE s.SNAME WHEN ''' + SNAME + ''' THEN g.[Score] END) [' + SNAME + ']'
FROM (SELECT SNAME FROM [Subject]) AS tmp
SELECT @strSQL = @strSQL + ' FROM [Score] g,[Subject] s, [Student] t WHERE g.SID=s.SID AND g.STUID = t.STUID GROUP BY t.STUID, t.STUNAME'
declare @strsql varchar(100)
