CREATE PRoc p_qry @TableName sysname, --表名 @纵轴 sysname, --交叉表最左面的列 @横轴 sysname, --交叉表最上面的列 @表体内容 sysname, --交叉表的数数据字段 @是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计 @是否家纵向合计 bit, --为1时在交叉表纵向最下边加纵向合计 @where varchar(400) --查询where条件 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+'] where ' + @where + ' group by ['+@纵轴+']'+@sum3)GO |
