生成交叉表的简单通用存储过程

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

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时在交叉表纵向最下边加纵向合计asdeclare @s nvarchar(4000),@sql varchar(8000)

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段set @s='declare @a sysnameif(select case when count(distinct ['+@纵轴+'])<count(distinct ['+@横轴+']) then 1 else 0 endfrom ['+@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

 

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: 存储 sql object go
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭