类似 Access 的交叉表存储过程

/* 转置交叉表 By spm 2004-12-20 */

CREATE PROCEDURE sp_TRANSFORM
@TabName nvarchar(255),
@AggreCol nvarchar(255),
@SelectCol nvarchar(255),
@PivotCol nvarchar(255),
@AggreFun nvarchar(20) = 'SUM',
@RightTotal bit = 0,
@LastTotal bit = 0,
AS
DECLARE @FinalSQL nvarchar(4000)

IF @AggreFun NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN
RAISERROR ('无效的聚合函数名： %s', 10, 1, @AggreFun)
END
ELSE
BEGIN
SET @FinalSQL = 'DECLARE @PivotSQL VARCHAR(8000);SET @PivotSQL = '''';'
SET @FinalSQL = @FinalSQL + 'SELECT @PivotSQL = @PivotSQL + '', ['' + RTRIM(' + @PivotCol + ') + ''] = ' + @AggreFun + '(CASE ' + @PivotCol + ' WHEN '''''' + ' + @PivotCol + ' + '''''' THEN ' + @AggreCol + ' ELSE 0 END)'' FROM (SELECT DISTINCT ' + @PivotCol + ' FROM ' + @TabName + ') PivotCOLUMN;'
SET @FinalSQL = @FinalSQL + 'SELECT(''SELECT ' + @SelectCol + ''' + @PivotSQL + '''

--增加右侧总计
IF @RightTotal = 1 SET @FinalSQL = @FinalSQL + ',小计=' + @AggreFun + '(' + @AggreCol + ')'

SET @FinalSQL = @FinalSQL + ' FROM ' + @TabName + ' GROUP BY ' + @SelectCol + ''

--增加末行总计
IF @LastTotal = 1 SET @FinalSQL = @FinalSQL + ' WITH CUBE'

SET @FinalSQL = @FinalSQL + ''')'

--执行动态 SQL
EXEC(@FinalSQL)
END
GO

---------------------------------------
我改写了一下，用临时表，大家看看有什么需要修改的地方
/* 转置交叉表 By spm 2004-12-24 Merry X'Max */

CREATE PROCEDURE sp_TRANSFORM
@TabName nvarchar(255),
@AggreCol nvarchar(255),
@SelectCol nvarchar(255),
@PivotCol nvarchar(255),
@AggreFun nvarchar(20) = 'SUM',
@RightTotal bit = 0,
@LastTotal bit = 0
AS
DECLARE @FinalSQL nvarchar(4000)

IF @AggreFun NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN
RAISERROR ('无效的聚合函数名： %s', 10, 1, @AggreFun)
END
ELSE
BEGIN
SET @FinalSQL = 'DECLARE @PivotSQL VARCHAR(8000);SET @PivotSQL = '''';'
SET @FinalSQL = @FinalSQL + 'SELECT @PivotSQL = @PivotSQL + '', ['' + RTRIM(' + @PivotCol + ') + ''] = ' + @AggreFun + '(CASE ' + @PivotCol + ' WHEN '''''' + ' + @PivotCol + ' + '''''' THEN ' + @AggreCol + ' ELSE 0 END)'' FROM (SELECT DISTINCT ' + @PivotCol + ' FROM ' + @TabName + ') PivotCOLUMN;'
SET @FinalSQL = @FinalSQL + 'EXEC(''SELECT ' + @SelectCol + ''' + @PivotSQL + '''

--增加右侧总计
IF @RightTotal = 1 SET @FinalSQL = @FinalSQL + ',小计=' + @AggreFun + '(' + @AggreCol + ')'

SET @FinalSQL = @FinalSQL + ' INTO ##temp FROM ' + @TabName + ' GROUP BY ' + @SelectCol + ''

--增加末行总计
IF @LastTotal = 1 SET @FinalSQL = @FinalSQL + ' WITH CUBE'

SET @FinalSQL = @FinalSQL + ''')'

--执行动态 SQL
EXEC(@FinalSQL)
SELECT * FROM ##temp
DROP TABLE ##temp
END
GO