MSSQL交叉表查询

调试时注意：
1、构成strsql语句时，“from”前后加空格
2、目标表的分组字段内容不能有空格

运行时：
exec corss ‘test’,’所在部门’,’员工姓名’,’销售业绩’,’sum’

创建存储过程：
CREATE procedure [dbo].[corss]
@strtabname as nvarchar(50),
@strcol as nvarchar(50),
@strgroup as nvarchar(50),
@strnumber as nvarchar(50),
@strsum as nvarchar(50)
as
declare @strsql as nvarchar(2000),@strtmpcol as nvarchar(1000)
execute ('declare corss_cursor cursor for select distinct '+@strcol+' from '+@strtabname+' for read only')
begin
set nocount on
set @strsql='select '+@strgroup+','+@strsum+'('+@strnumber+')as['+@strnumber+']'
open corss_cursor
while(0=0)
begin
fetch next from corss_cursor into @strtmpcol
if(@@fetch_status<>0)break
set @strsql=@strsql+','+@strsum+'(case '+@strcol+' when '''+@strtmpcol+''' then '+@strnumber+' else 0 end) as ['+@strtmpcol+']'
end
set @strsql=@strsql+' from '+@strtabname+' group by '+@strgroup
execute(@strsql)
if @@error<>0 return @@error
close corss_cursor
deallocate corss_cursor return 0
end