限制列数的交叉表.sql

sqlserver 数据库编程的绝好脚本

/*--假设有数据

科目   学生1_上学期  学生1_下学期 学生2_上学期 学生2_下学期  学生3_上学期  学生3_下学期 学生4_上学期  学生4_下学期 学生5_上学期 学生5_下学期     
------ ------------ -----------  ----------- ------------ ------------ ------------ ------------ ----------- ------------ ----------- 
语文   80           90           30          69           87           65           60           60          87           65
数学   45           43           43          98           0            5            70           70          0            5


现要需要一个存储过程,输入每页的学生数目,得到结果,假设输入2,得到结果:
页号          科目         学生1_上学期     学生1_下学期     学生2_上学期     学生2_下学期     
----------- ---------- ----------- ----------- ----------- ----------- 
0           语文         80          90          30          69
0           数学         45          43          43          98
1           语文         87          65          60          60
1           数学         0           5           70          70
2           语文         87          65          NULL        NULL
2           数学         0           5           NULL        NULL

（所影响的行数为 6 行）

--*/
create table 表A(科目 varchar(10)
	,学生1_上学期 int,学生1_下学期 int
	,学生2_上学期 int,学生2_下学期 int
	,学生3_上学期 int,学生3_下学期 int
	,学生4_上学期 int,学生4_下学期 int
	,学生5_上学期 int,学生5_下学期 int)
insert into 表A
select '语文',80,90,30,69,87,65,60,60,87,65
union all select '数学',45,43,43,98,0,5,70,70,0,5
go

--处理的存储过程
create proc p_qry
@pagesize int		--每页要显示的学生记录数
as
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
	,@i int,@j int
select @s1='',@s2='',@s3=''

select gid=cast((colid-2)/@pagesize/2 as varchar),name into #t
from syscolumns 
where object_id('表A')=id and name<>'科目' order by colid
set @j=@pagesize-((@@rowcount/2) % @pagesize)

select @s1=case @i when gid then @s1 else @s1+',@'+gid+' varchar(8000)' end
	,@s2=@s2+case @i when gid then '' else ' from 表A'',@'+gid+'=''select 页号='+gid+',科目' end+',['+name+']'
	,@s3=case @i when gid then @s3 else @s3+'+'' union all ''+@'+gid end
	,@i=gid
from #t

while @j>0
	select @s2=@s2+',null,null',@j=@j-1

select @s1=substring(@s1,2,8000)
	,@s2=substring(@s2,11,8000)+' from 表A'''
	,@s3='exec('+substring(@s3,16,8000)+')'

exec('declare '+@s1+'
select '+@s2+'
'+@s3)
go

--调用
exec p_qry 2
go

--测试测试环境
drop table 表A
drop proc p_qry