1：数据准备：
create table subject(id int,name nvarchar(20))
insert into subject(id,name) values(1,'语文')
insert into subject(id,name) values(2,'数学')
insert into subject(id,name) values(3,'物理')
insert into subject(id,name) values(4,'化学')
insert into subject(id,name) values(5,'英语')
select * from subject
create table stusubscore(stuname nvarchar(50),subjectid int,score decimal(9,2))
insert into stusubscore(stuname,subjectid ,score) values('张山',1,200.5)
insert into stusubscore(stuname,subjectid ,score) values('李四',2,20)
insert into stusubscore(stuname,subjectid ,score) values('赵六',5,1.5)
insert into stusubscore(stuname,subjectid ,score) values('李四',1,20)
insert into stusubscore(stuname,subjectid ,score) values('赵六',1,1.5)
insert into stusubscore(stuname,subjectid ,score) values('王二',3,1.5)
insert into stusubscore(stuname,subjectid ,score) values('许三',4,1.5)
select * from stusubscore
其中为了搞得简单些，就没有对学生成绩表中的学生直接用学生姓名来处理了。
查询出来的基础数据就是：
科目表：
id name
1 语文
2 数学
3 物理
4 化学
5 英语
学生科目成绩表
stuname subjectid score
张山 1 200.50
李四 2 20.00
赵六 5 1.50
李四 1 20.00
赵六 1 1.50
王二 3 1.50
许三 4 1.50
2：转置的过程：
Create PROCEDURE [DBO].[stusubscorestatus]
as
declare @tmpstr nvarchar(4000)
declare @resultstr nvarchar(4000)
declare @id int
declare @name nvarchar(20)
create table #tp(stuname nvarchar(50))
select @resultstr = 'select stuname as 学生姓名,'
DECLARE IO_Cursor CURSOR FOR select id,name from subject
OPEN IO_Cursor
WHILE (0=0)
BEGIN
FETCH NEXT FROM IO_Cursor into @id,@name
if @@FETCH_STATUS<>0 break
select @tmpstr = 'alter Table #tp ADD ['+ @name + '] decimal(9,2)'
execute(@tmpstr)
select @tmpstr = 'insert into #tp(stuname,['+ @name + '])
select stuname,sum(score) from stusubscore
where subjectid= '+ cast(@id as varchar(10)) +' group by stuname'
execute(@tmpstr)
select @resultstr = @resultstr + 'sum('+ @name + ') as ' + @name +','
end
CLOSE IO_Cursor
DEALLOCATE IO_Cursor
select @resultstr = substring(@resultstr,1,len(@resultstr)-1)
select @resultstr = @resultstr +' from #tp group by stuname'
execute(@resultstr)
drop table #tp
