通用动态交叉表的实现

-- ============================================================================
-- 通用动态交叉表的实现
-------------------------------------------------------------------------------
-- usp_Crosstab
-------------------------------------------------------------------------------

create PROCEDURE usp_Crosstab
@table varchar(50), --表名
@sumRow varchar(8000),--合计列字段
@groupRow varchar(1000), --分组列字段
@col varchar(50), --要将列值转换为列的列字段
@colTable varchar(1000),--要将列值转换为列的列字段表
@Sortfieldvarchar(100),--要将列值转换为列的列字段表排序字段
@content varchar(50), --列值转换为列后列的内容字段
@showSum varchar(50),--显示总计列名
@showSubtotal varchar(50),--显示小计列
@wherevarchar(8000)--查询条件
AS
DECLARE @declareSql varchar(8000), --变量定义的SQL
@initSql varchar(8000), --初始变量的SQL
@SubsectionSQL varchar(8000), --读取以4000分段后数据的SQL
@CombinationSQL varchar(8000), --分段后各个变量组合的SQL
@EndFromSQL varchar(8000), --组合的后辍(表名、分组列名)的SQL
@i int, --根据4000为分段迭代得到的i
@ic varchar(50), --转成字符串后的i
@mc varchar(50) --变量的个数

EXEC('select ID=Identity(int,1,1),COL=''sum(case '+@col+' when ''''''+'+@col+'+'''''' then '+@content+' else 0 end) as ''''''+'+@col+'+'''''','' ,GID=0,Sort= '+@Sortfield+' into ##tmp from (select '+@col+'=replace('+@col+','''''''',''''''''''''),'+@Sortfield+' from '+@colTable+')a')

SELECT @i=MAX(len(COL)) FROM ##tmp
SET @i=4000/@i

UPDATE ##tmp SET GID=ID/@i

SELECT @i=MAX(GID),@mc=cast(@i AS varchar(50)) FROM ##tmp
SELECT @declareSql='',@initSql='',@SubsectionSQL='',@CombinationSQL='',@EndFromSQL=''


WHILE @i>=0
BEGIN
SELECT @ic=cast(@i AS varchar(50)),@i=@i-1,@declareSql='@'+@ic+' as varchar(4000),'+@declareSql,@initSql='@'+@ic+'='''','+@initSql,
@SubsectionSQL='select @'+@ic+'=@'+@ic+'+COL from ##tmp where gid='''+@ic+''' order by sort '+char(13)+@SubsectionSQL,@CombinationSQL='@'+@ic+'+'+@CombinationSQL
END

SET @EndFromSQL=''' into ##CrossTable from '+@table+' where '+@where+' group by '+@groupRow+''''
SELECT @declareSql='declare '+left(@declareSql,len(@declareSql)-1)+char(13),@initSql='select '+left(@initSql,len(@initSql)-1)+char(13),@CombinationSQL=left(@CombinationSQL,len(@CombinationSQL)-1)
EXEC(@declareSql+@initSql+@SubsectionSQL+
'set @'+@mc+'=left(@'+@mc+',len(@'+@mc+')-1)'+
'exec(''select '+@sumRow+',''+'+@CombinationSQL+'+'+@EndFromSQL+')'
)

EXEC('UPDATE ##CrossTable SET '+@showSum+' = ''总计'', '+@showSubtotal+' = null WHERE '+@showSum+' IS NULL ')

SELECT * FROM ##CrossTable
DROP TABLE ##tmp
DROP TABLE ##CrossTable

GO