类似 Access 的交叉表存储过程,但不能直接把查询结果输出成表,请大家改改

sunpm 2004-12-24 11:25:29
/* 转置交叉表 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

可惜执行的时候不能直接把结果表直接输出,大家看看能不能转成输出表的函数
还有就是看看有什么可以精简或者提高效率的地方,集思广益哈。。。

我一定送分哟!
...全文
282 点赞 收藏 17
写回复
17 条回复
scal_inc 2004年12月24日
up
回复 点赞
sunpm 2004年12月24日
/* 转置交叉表 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

可惜执行的时候不能直接把结果表直接输出,大家看看能不能转成输出表的函数
还有就是看看有什么可以精简或者提高效率的地方,集思广益哈。。。

我一定送分哟!
回复 点赞
dumega 2004年12月24日
建议楼主先整理下板式
回复 点赞
sunpm 2004年12月24日
我改写了一下,用临时表,大家看看有什么需要修改的地方
/* 转置交叉表 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
回复 点赞
sunpm 2004年12月24日
但为什么我在asp里执行这个储存过程,没有返回集呢?
回复 点赞
vinsonshen 2004年12月24日
存储过程当然能返回记录集啦~~
回复 点赞
sunpm 2004年12月24日
我在问个问题,函数可以返回表,存储过程能返回记录集么?
回复 点赞
balloonman2002 2004年12月24日
最后插入到临时表,再从临时表select出来不就是结果集吗?
回复 点赞
sunpm 2004年12月24日
拼出来的 SQL 是没有错的,可以返回结果,我的意思是说想将结果弄成记录集,方便用DBGrid显示
回复 点赞
sunpm 2004年12月24日
我又整理了一下,刚才有点错误

/* 转置交叉表 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 + ' FROM ' + @TabName + ' GROUP BY ' + @SelectCol + ''

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

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

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

回复 点赞
sunpm 2004年12月24日
哦,大家能不能想办法把这个存储过程改成 Returns 为 Table 的函数那?
我把这个存储过程最终拼成的 SQL 写出来,其实就是大家常用的行列转换,主要我这要处理的太多了,想编个函数。
DECLARE @PivotSQL VARCHAR(8000);
SET @PivotSQL = '';
SELECT @PivotSQL = @PivotSQL + ', [' + RTRIM(品名) + '] = SUM(CASE 品名 WHEN ''' + 品名 + ''' THEN 数量 ELSE 0 END)' FROM (SELECT DISTINCT 品名 FROM VIEW_领用月报表) PivotCOLUMN;
EXEC('SELECT Dep_Name' + @PivotSQL + ' FROM VIEW_领用月报表 GROUP BY Dep_Name')
回复 点赞
balloonman2002 2004年12月24日
临时表用#打头,当会话结束后数据库会自动清除
如果用tempdb..打头,当服务重起后会自动清除

无需担心其回收问题
回复 点赞
sunpm 2004年12月24日
我用临时表实现了的,但是不太方便,而且临时表的回收又是一个问题
回复 点赞
progrose 2004年12月24日
我这没有sqlserver, 你在exec前,将你拼成的sql显示出来,看看是不是有问题。
回复 点赞
WorldMobile 2004年12月24日
可以的,你需要创建一个临时表,然后把数据写到临时表里

在前台用的时候直接对临时表select即可。
回复 点赞
balloonman2002 2004年12月24日
看了就头大,建议放到DW里去处理
回复 点赞
sunpm 2004年12月24日
up
回复 点赞
发动态
发帖子
应用实例
创建于2007-09-28

1.1w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例
社区公告
暂无公告