22,298
社区成员
发帖
与我相关
我的任务
分享邹建大哥，我想写一个通用的针对单表用的交叉表存储过程！(挑战性)
我想写一个通用的针对单表用的交叉表存储过程
传入几个参数：
@TableName varchar(16) --表名
@纵轴 varchar(20) --交叉表最左面的列
@横轴 varchar(10) --交叉表最上面的列
@表体内容 numeric(10,2) --交叉表的数字内容
@是否加横向合计 bit --为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
思路：
1、取得disinct 横轴字段 后，取得唯一的横轴字段表
2、根据横轴的唯一字段内容，循环整个表后动态生成一个Sql语句，
像select 科室,sum(case(横轴字段表.....).......) from @tablename group
by 科室
exec 生成的Sql
3、根据参数是否合计，分别加合计字段，求出横向合计和纵向合计
您看这个思路行吗？但有一个限制就是横轴不能太多，多了Sql可能会超过8000字符。一般不会这么多，如果太多就把横轴变为纵轴，总之取字段较少的做横轴，这个就是传参数时的问题了。
如果弄成了，这个在一定的范围内应该是比较通用的了。对不！
这是我的思路，具体写的时候，感觉到Sql的组合比较麻烦，能帮我写一下吗？
传入几个参数：
@TableName varchar(16) --表名
@纵轴 varchar(20) --交叉表最左面的列
@横轴 varchar(10) --交叉表最上面的列
@表体内容 numeric(10,2) --交叉表的数字内容
@是否加横向合计 bit --为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
思路：
1、取得disinct 横轴字段 后，取得唯一的横轴字段表
2、根据横轴的唯一字段内容，循环整个表后动态生成一个Sql语句，
像select 科室,sum(case(横轴字段表.....).......) from @tablename group
by 科室
exec 生成的Sql
3、根据参数是否合计，分别加合计字段，求出横向合计和纵向合计
您看这个思路行吗？但有一个限制就是横轴不能太多，多了Sql可能会超过8000字符。一般不会这么多，如果太多就把横轴变为纵轴，总之取字段较少的做横轴，这个就是传参数时的问题了。
如果弄成了，这个在一定的范围内应该是比较通用的了。对不！
这是我的思路，具体写的时候，感觉到Sql的组合比较麻烦，能帮我写一下吗？
...全文
请发表友善的回复…
发表回复
5isoft 2004-07-14
- 打赏
- 举报
邹建，我未婚
normandj 2004-07-01
- 打赏
- 举报
佩服佩服
icedut 2004-06-30
- 打赏
- 举报
收藏
zmlrong 2004-06-30
- 打赏
- 举报
在这里，我真的要感谢谢，邹建大哥！
让人学习成长迅速，并能举实例，通用性强！
我相信高手很多，但这种时时为四海兄弟姐妹，排忧解难的高手，
才是高手中的高手，真是难得！
我心里头只有一个字 – 神 ，我真想结识这位神人哟！佩服！
我也真诚希望大家多提意见，互相解决问题，共同分享成功的喜悦！
我的QQ：178819198，渴望有幸能结识爱好数据库的朋友！
让人学习成长迅速，并能举实例，通用性强！
我相信高手很多，但这种时时为四海兄弟姐妹，排忧解难的高手，
才是高手中的高手，真是难得！
我心里头只有一个字 – 神 ，我真想结识这位神人哟！佩服！
我也真诚希望大家多提意见，互相解决问题，共同分享成功的喜悦！
我的QQ：178819198，渴望有幸能结识爱好数据库的朋友！
guhguh 2004-06-30
- 打赏
- 举报
强,学习啊,
ilovesong 2004-06-30
- 打赏
- 举报
顶，让大家多看看。
wdyxt 2004-06-30
- 打赏
- 举报
学习
ilovesong 2004-06-29
- 打赏
- 举报
神了！最好是能对横轴进行排序！
如:会计报表常用的横轴（1月、2月… … 或 一月、二月… … ）
----------------------------
如果这样的话，1月、10月、11月、12月、2月。。。。。
阿拉伯数字就是这么排序的，否则就得改算法，加临时表才行了。
大家多提意见，让邹建大哥搞一个超通用、好用的经典sql交叉表。是不是，邹建大哥，考验你的时候就要来了。
如:会计报表常用的横轴（1月、2月… … 或 一月、二月… … ）
----------------------------
如果这样的话，1月、10月、11月、12月、2月。。。。。
阿拉伯数字就是这么排序的，否则就得改算法，加临时表才行了。
大家多提意见，让邹建大哥搞一个超通用、好用的经典sql交叉表。是不是，邹建大哥，考验你的时候就要来了。
zanglinfeng 2004-06-29
- 打赏
- 举报
佩服佩服
Greenleaf 2004-06-29
- 打赏
- 举报
佩服佩服
zmlrong 2004-06-29
- 打赏
- 举报
神了！最好是能对横轴进行排序！
如:会计报表常用的横轴（1月、2月… … 或 一月、二月… … ）
如:会计报表常用的横轴（1月、2月… … 或 一月、二月… … ）
ilovesong 2004-06-28
- 打赏
- 举报
试了一下，速度不错哦。邹建大哥看看还有需要完善的功能吗？准备揭帖了。
ilovesong 2004-06-27
- 打赏
- 举报
邹建大哥，真是俺的偶像。十分佩服。
你感觉这个存储过程已经是非常完善了吗？关于这个问题，还有需要完善和考虑的问题吗？
你感觉这个存储过程已经是非常完善了吗？关于这个问题，还有需要完善和考虑的问题吗？
zjcxc 2004-06-25
- 打赏
- 举报
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_qry]
GO
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
--邹建 204.06--*/
/*--调用示例
exec p_qry 'syscolumns','id','colid','colid','name like ''s%''',1,1
--*/
create proc p_qry
@TableName sysname, --表名
@纵轴 sysname, --交叉表最左面的列
@横轴 sysname, --交叉表最上面的列
@表体内容 sysname, --交叉表的数数据字段
@条件 varchar(1000),--查询的处理条件
@是否加横向合计 bit, --为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)
--规范条件
set @条件=case when @条件<>'' then ' where ('+@条件+')' else '' end
--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s='declare @a sysname
if(select case when count(distinct ['+@纵轴+'])<count(distinct ['+@横轴+']) then 1 else 0 end
from ['+@TableName+'] '+@条件+')=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a'
exec sp_executesql @s
,N'@纵轴 sysname out,@横轴 sysname out'
,@纵轴 out,@横轴 out
--生成交叉表处理语句
set @s='
set @s=''''
select @s=@s+'',[''+cast(['+@横轴+'] as varchar)+'']=sum(case ['+@横轴
+'] when ''''''+cast(['+@横轴+'] as varchar)+'''''' then ['+@表体内容+'] else 0 end)''
from ['+@TableName+']
'+@条件+'
group by ['+@横轴+']'
exec sp_executesql @s
,N'@s varchar(8000) out'
,@sql out
--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then ',[合计]=sum(['+@表体内容+'])'
else '' end
,@sum2=case @是否家纵向合计
when 1 then '['+@纵轴+']=case grouping(['
+@纵轴+']) when 1 then ''合计'' else cast(['
+@纵轴+'] as varchar) end'
else '['+@纵轴+']' end
,@sum3=case @是否家纵向合计
when 1 then ' with rollup'
else '' end
--生成交叉表
exec('select '+@sum2+@sql+@sum1+'
from ['+@TableName+']
'+@条件+'
group by ['+@纵轴+']'+@sum3)
go
ilovesong 2004-06-24
- 打赏
- 举报
邹建大哥，果然高明，可是我忘了一个参数，就是条件参数，看能否加上。
@cWhere sysname
如：where 日期 between ? and ?
或者是别的条件，因为这个条件是不确定的过滤。
佩服。
@cWhere sysname
如：where 日期 between ? and ?
或者是别的条件，因为这个条件是不确定的过滤。
佩服。
zjcxc 2004-06-23
- 打赏
- 举报
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_qry]
GO
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
--邹建 204.06--*/
/*--调用示例
exec p_qry 'syscolumns','id','colid','colid',1,1
--*/
create proc p_qry
@TableName sysname, --表名
@纵轴 sysname, --交叉表最左面的列
@横轴 sysname, --交叉表最上面的列
@表体内容 sysname, --交叉表的数数据字段
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)
--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s='declare @a sysname
if(select case when count(distinct ['+@纵轴+'])<count(distinct ['+@横轴+']) then 1 else 0 end
from ['+@TableName+'])=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a'
exec sp_executesql @s
,N'@纵轴 sysname out,@横轴 sysname out'
,@纵轴 out,@横轴 out
--生成交叉表处理语句
set @s='
set @s=''''
select @s=@s+'',[''+cast(['+@横轴+'] as varchar)+'']=sum(case ['+@横轴
+'] when ''''''+cast(['+@横轴+'] as varchar)+'''''' then ['+@表体内容+'] else 0 end)''
from ['+@TableName+']
group by ['+@横轴+']'
exec sp_executesql @s
,N'@s varchar(8000) out'
,@sql out
--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then ',[合计]=sum(['+@表体内容+'])'
else '' end
,@sum2=case @是否家纵向合计
when 1 then '['+@纵轴+']=case grouping(['
+@纵轴+']) when 1 then ''合计'' else cast(['
+@纵轴+'] as varchar) end'
else '['+@纵轴+']' end
,@sum3=case @是否家纵向合计
when 1 then ' with rollup'
else '' end
--生成交叉表
exec('select '+@sum2+@sql+@sum1+'
from ['+@TableName+']
group by ['+@纵轴+']'+@sum3)
go
drop procedure [dbo].[p_qry]
GO
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
--邹建 204.06--*/
/*--调用示例
exec p_qry 'syscolumns','id','colid','colid',1,1
--*/
create proc p_qry
@TableName sysname, --表名
@纵轴 sysname, --交叉表最左面的列
@横轴 sysname, --交叉表最上面的列
@表体内容 sysname, --交叉表的数数据字段
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)
--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s='declare @a sysname
if(select case when count(distinct ['+@纵轴+'])<count(distinct ['+@横轴+']) then 1 else 0 end
from ['+@TableName+'])=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a'
exec sp_executesql @s
,N'@纵轴 sysname out,@横轴 sysname out'
,@纵轴 out,@横轴 out
--生成交叉表处理语句
set @s='
set @s=''''
select @s=@s+'',[''+cast(['+@横轴+'] as varchar)+'']=sum(case ['+@横轴
+'] when ''''''+cast(['+@横轴+'] as varchar)+'''''' then ['+@表体内容+'] else 0 end)''
from ['+@TableName+']
group by ['+@横轴+']'
exec sp_executesql @s
,N'@s varchar(8000) out'
,@sql out
--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then ',[合计]=sum(['+@表体内容+'])'
else '' end
,@sum2=case @是否家纵向合计
when 1 then '['+@纵轴+']=case grouping(['
+@纵轴+']) when 1 then ''合计'' else cast(['
+@纵轴+'] as varchar) end'
else '['+@纵轴+']' end
,@sum3=case @是否家纵向合计
when 1 then ' with rollup'
else '' end
--生成交叉表
exec('select '+@sum2+@sql+@sum1+'
from ['+@TableName+']
group by ['+@纵轴+']'+@sum3)
go
