实现数据的矩阵转换运算

/* ----------------------------------*/
/* 功能 ： 实现数据的矩阵转换运算?????? */
/* ----------------------------------*/
if exists(select name from sysobjects where xtype='p' and name='p_jzcaculate')
?? drop procedure p_jzcaculate
go
create procedure p_jzcaculate
@tablename varchar(200),
@filter varchar(400)
as
begin
? set nocount on
? declare @col nvarchar(256)
? declare @makesql nvarchar(4000)
? declare @insertsql nvarchar(4000)
? declare @caculatesql nvarchar(400)
? declare @count int
? declare @i int
? declare @j int
? declare @colid int
? create table #TempTable (colname nvarchar(20))
? select @caculatesql = 'select @count=count(1) from ' + @tablename +@filter
? exec sp_executesql @caculatesql, N'@count int output',@count output
? if @count >=1024
? begin
??? Raiserror('表的行数太多，程序无法运算',16,1)
? end
? else
? begin
??? select @i=0
??? while @count >0
??? begin
????? select @i=@i+1
????? select @makesql = 'alter table #TempTable add col'+convert(varchar(20),@i)+' varchar(40)'
????? exec(@makesql)
????? select @count=@count-1???
??? end
??? declare my_cursor cursor for
??? select name,colid from syscolumns where id=object_id(@tablename) order by colid
??? open my_cursor
??? fetch next from my_cursor into @col,@colid
??? while @@fetch_status = 0
??? begin
????? select @j=count(1) from syscolumns where id=object_id(@tablename)
????? if @colid<@j-1
????? begin
????? select @makesql ='select @insertsql= @insertsql + case
?????? when convert(varchar(400),'+@col+') is null then '''' else convert(varchar(400),'+@col+') end +'','' from ' +@tablename +@filter
????? select @insertsql =N'insert #TempTable values ('''+@col+ ','
????? execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
????? select @insertsql = replace(left(@insertsql,len(@insertsql)-1),',',''',''') +''')'
?????? --select @insertsql
?????? -- print @makesql
????? --? exec(@makesql)
????? exec(@insertsql)
???? end
??? --? if @@error<>0
??? --? print @@error
????? fetch next from my_cursor into @col,@colid
??? end
??? close my_cursor
??? deallocate my_cursor
??? select * from #TempTable
??? set nocount off
??? drop table #TempTable
? end
end
go

调用：

exec p_jzcaculate 'CABLEINFO_jydz',' '