set
ANSI_NULLS
ON
set
QUOTED_IDENTIFIER
ON
go
/**/
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
*/
/**/
/*--调用示例
execp_qry'syscolumns','id','colid','colid','namelike''s%''',1,1
--*/
ALTER
proc
[
dbo
]
.
[
proc_ViewDriverDicpatch
]
@TableName
sysname,
--
表名
@纵轴
sysname,
--
交叉表最左面的列
@横轴
sysname,
--
交叉表最上面的列
@表体内容
sysname,
--
交叉表的数数据字段
@条件
varchar
(
1000
),
--
查询的处理条件
@是否加横向合计
bit
,
--
为1时在交叉表横向最右边加横向合计
@是否加纵向合计
bit
--
为1时在交叉表纵向最下边加纵向合计
as
declare
@s
nvarchar
(
4000
),
@sql
varchar
(
8000
)
--
规范条件
/**/
/*set@条件=casewhen@条件<>''then'where('+@条件+')'else''end*/
--
判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
/**/
/*
set@s='declare@asysname
if(selectcasewhencount(distinct[@纵轴])from'+@TableName+')=1
select@a=@纵轴,@纵轴=@横轴,@横轴=@a'
execsp_executesql@s
,N'@纵轴sysnameout,@横轴sysnameout'
,@纵轴out,@横轴out
*/
--
生成交叉表处理语句
set
@s
=
'
set@s=
''''
select@s=@s+
''
,[
''
+cast([
'
+
@横轴
+
'
]asvarchar)+
''
]=sum(case[
'
+
@横轴
+
'
]when
''''''
+cast([
'
+
@横轴
+
'
]asvarchar)+
''''''
then[
'
+
@表体内容
+
'
]else0end)
''
from[
'
+
@TableName
+
'
]
'
+
@条件
+
'
groupby[
'
+
@横轴
+
'
]
'
exec
sp_executesql
@s
,N
'
@svarchar(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
'
[
'
+
@纵轴
+
'
]=casegrouping([
'
+
@纵轴
+
'
])when1then
''
合计
''
elsecast([
'
+
@纵轴
+
'
]asvarchar)end
'
else
'
[
'
+
@纵轴
+
'
]
'
end
,
@sum3
=
case
@是否加纵向合计
when
1
then
'
withrollup
'
else
''
end
--
生成交叉表
exec
(
'
select
'
+
@sum2
+
@sql
+
@sum1
+
'
from[
'
+
@TableName
+
'
]
'
+
@条件
+
'
groupby[
'
+
@纵轴
+
'
]
'
+
@sum3
)
