99.固定行列报表

--测试数据
create table test (TEST_NO varchar(10),TEST_NAME varchar(10))
insert into test select 'A0001','张三'
insert into test select 'A0003','李４'
insert into test select 'A0004','李五'
insert into test select 'B0002','王五'
insert into test select 'B0003','将李'
insert into test select 'B0004','五二'
insert into test select 'C0001','王二'
insert into test select 'C0003','邓三'
insert into test select 'C0004','刘４'
go

--查询处理的存储过程
CREATE PROC p_PageView
@tbname sysname,   --查询的表名
@KeyField sysname, --关键字段名
@SortField sysname,--排序字段名
@Columns int=10,   --每页最多显示的列数
@Rows int=10       --每页最多显示的行数
as
set nocount on
declare @s nvarchar(4000),@gid1 varchar(20),@gid2 varchar(20),@pageno varchar(20)
select @s='',
	@pageno='1+gid/'+rtrim(@Columns*@rows),
	@gid1='(gid%'+rtrim(@Columns*@rows)+')/'+rtrim(@rows),
	@gid2='(gid%'+rtrim(@Columns*@rows)+')%'+rtrim(@rows)
while @Columns>0
	select @Columns=@Columns-1,
		@s='
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NO end),'''')'
		+'
,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NAME end),'''')'
		+@s
exec('select PageNo='+@pageno+@s+' from(
	select TEST_NO,TEST_NAME,
		gid=(select count(*) from test where TEST_NO