mysql 交叉表 存储过程_通用交叉表存储过程

alter 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 ['+@纵轴+'])

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