第一种:
通用的针对单表用的交叉表存储过程
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></count>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
第二种:
一个通用的交叉制表存储过程
原作者:John Papa, Matthew Shepker1999
Use pubs
GO
exec prCrosstab 'stor_id','ord_date','qty','sales',1,5
结果:
RowHead | Year_1992 | Year_1993 | Year_1994 |
6380 | 0 | 0 | 8 |
7066 | 0 | 50 | 75 |
7067 | 80 | 0 | 10 |
7131 | 0 | 85 | 45 |
7896 | 0 | 60 | 0 |
8042 | 0 | 55 | 25 |
以下为生成脚本:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prCrosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prCrosstab]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
功能:交叉制表
参数 描述
@chrRowHead 表示列,在交叉制表的结果中作为第一出现
@chrColHead 表示列,在交叉制表的结果中该列中的数据被变换为新列名称
@chrValue 表示列,在该列中执行聚合函数
@chrSource 源表或视图
@inyType 1-求和,2-平均值,3-最小值,4-最大值,5-计数
@inyGrouping 1-工作日,2-年内的周数,3-月份,4-季度,5-年份
Author:John Papa Matthew Shepker
整理:水如烟(http://blog.csdn.net/lzmtw)
日期:<chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="5" year="2005"></chsdate>5-1-2005
*/
CREATE PROCEDURE prCrosstab
@chrRowHead char(30),
@chrColHead char(30),
@chrValue char(30),
@chrSource char(30),
@inyType tinyint=1,
@inyGrouping tinyint=0
AS
/* Variables for the procedure */
DECLARE
@chvRow varchar(255),
@chvCol varchar(255),
@chvVal varchar(255),
@chvType varchar(10),
@chvRowType varchar(10),
@chvColType varchar(255),
@chvTemp varchar(255),
@chvColTemp varchar(255),
@chvRowTemp varchar(255),
@intType int,
@intRowType int,
@intColType int,
@chvExec varchar(255),
@chvGroup varchar(255),
@fltTemp float,
@dtmTemp datetime,
@insR smallint,
@intColumn int,
@intReturn int,
@intTemp int,
@intColNameLen int,
@intMaxRowHead int
SET NOCOUNT ON
/* Check if source exists */
IF NOT EXISTS
(SELECT *
FROM sysobjects
WHERE name = @chrSource
AND type IN ('v','u'))
BEGIN
RAISERROR 51001 'Source does not exists.'
RETURN -1
END
/* Check for column existence */
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrColHead)
BEGIN
RAISERROR 51002 'Invalid @chrColHead name.'
RETURN -1
END
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrRowHead)
BEGIN
RAISERROR 51002 'Invalid @chrRowHead name.'
RETURN -1
END
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrValue)
BEGIN
RAISERROR 51002 'Invalid @chrValue name.'
RETURN -1
END
/* Verify type is valid (1(sum),2(avg),etc...) */
IF @inyType OR @inyType > 5
BEGIN
RAISERROR 51000 'Invalid crosstab type.'
RETURN -1
END
/* Create typestr to hold aggregate name */
SELECT @chvType=
CASE @inyType
WHEN 1 THEN 'SUM'
WHEN 2 THEN 'AVG'
WHEN 3 THEN 'MAX'
WHEN 4 THEN 'MIN'
WHEN 5 THEN 'COUNT'
ELSE 'SUM'
END
/* Getstandard data type of @chrValue column */
SELECT @chvTemp = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrValue
/* Categorize types for aggregate check */
SELECT @intTemp =
CASE
WHEN @chvTemp IN ('int','smallint','tinyint','float','real',
'decimal','numeric','monery','smallmoney') THEN 1
WHEN @chvTemp IN ('datetime','smalldatetime')THEN 3
WHEN @chvTemp IN ('bit','char','varchar') THEN 5
ELSE 100
END
/* validate existing data type is consistent with selected aggregate */
IF @inyType
BEGIN
RAISERROR 51020 'Crosstab type not valid with @chrValue definition.'
RETURN -1
END
/* Hold thedata type for future use */
SELECT @chvColType = RTRIM(
CASE @inyType
WHEN 5 THEN 'int'
ELSE CASE
WHEN @chvTemp IN ('bit','char','varchar') THEN 'int'
WHEN @chvTemp IN ('decimal','numeric') THEN 'float'
ELSE @chvTemp
END
END)
/* Verify grouping is valid for colhead */
IF @inyGrouping OR @inyGrouping > 5
BEGIN
RAISERROR 51010 'Invalid crosstab grouping.'
RETURN -1
END
/* Getstandard data type of @chrColHead column */
SELECT @chvTemp = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrColHead
IF UPPER(@chvTemp) NOT IN ('CHAR','VARCHAR')
SELECT @intColtype = 1
ELSE
SELECT @intColtype = 0
/* Get standard data type of @chvRowHead */
SELECT @chvRowType = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrRowHead
IF UPPER(@chvRowType) NOT IN ('CHAR','VARCHAR')
SELECT @intRowtype = 1
ELSE
SELECT @intRowtype = 0
/* Categorize types for grouping check */
SELECT @intTemp =
CASE
WHEN @chvTemp IN ('int','smallint','tinyint','float','real',
'decimal','numeric','monery','smallmoney') THEN 1
WHEN @chvTemp IN ('datetime','smalldatetime')THEN 3
WHEN @chvTemp IN ('bit','char','varchar') THEN 5
ELSE 100
END
/* Validate existing data type is consistant with selected grouping */
IF (@intTemp = 5 AND @inyGrouping > 0) OR (@intTemp = 1 AND @inyGrouping > 0)
OR (@intTemp =3 AND @inyGrouping =0)
BEGIN
RAISERROR 51030 'Crosstab grouping not valid with @chvColHead definition.'
RETURN -1
END
/* Check for permission on source */
IF user_id() 1
BEGIN
IF (SELECT COUNT(DISTINCT c.name)
FROM syscolumns c,sysobjects o,sysprotects p,
sysusers u,master..spt_values v
WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)
AND c.id = o.id
AND p.id = c.id
AND c.colid = v.number
AND v.type = 'p'
AND o.id = object_id(@chrSource)
AND (u.uid = user_id() OR u.uid IN
(SELECT u1.uid
FROM sysusers u1
WHERE u1.gid = u1.uid
AND u1.gid IN
(SELECT u2.gid
FROM sysusers u2
WHERE u2.uid = user_id()
OR u2.uid = user_id('public'))))
AND p.uid = u.uid
AND p.action = 193
AND p.protecttype = 205
AND columns IS NOT NULL
AND CASE SUBSTRING(p.columns,1,1) & 1
WHEN null THEN 255
WHEN 0 THEN CONVERT(tinyint,SUBSTRING(p.columns,v.low,1))
ELSE (CONVERT(tinyint,ISNULL(SUBSTRING(p.columns,v.low,1),0)))
END & v.high 0
AND NOT EXISTS
(SELECT *
FROM syscolumns c5,sysobjects o5,
sysprotects p5,sysusers u5,
master..spt_values v5
WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)
AND c5.colid = c.colid
AND c5.id = c5.id
AND c5.colid = v5.number
AND v5.type = 'p'
AND o5.id = object_id(@chrSource)
AND (u5.uid = user_id() OR u5.uid IN
(SELECT u6.uid
FROM sysusers u6
WHERE u6.gid = u6.uid
AND u6.gid IN
(SELECT u7.gid
FROM sysusers u7
WHERE u7.uid = user_id()
OR u7.uid = user_id('public'))))
AND p5.uid = u5.uid
AND p5.action = 193
AND p5.protecttype = 206
AND p5.columns IS NOT NULL
AND CASE SUBSTRING(p5.columns,1,1) & 1
WHEN NULL THEN 255
WHEN 0 THEN CONVERT(tinyint,SUBSTRING(p5.columns,v5.low,1))
ELSE (CONVERT(tinyint,ISNULL(SUBSTRING(p5.columns,v5.low,1),0)))
END & v5.high 0)) 3
BEGIN
RAISERROR 51003 'Permission denied on column.'
RETURN -1
END
END
/* Create table #colnames and #rownames */
CREATE TABLE #colnames (colname varchar(255) NULL,colnumber int NULL)
CREATE TABLE #rownames (rowname varchar(255) NULL)
/* Insert distinct column data into #colnames */
SELECT @chvExec = 'insert #colnames select col1,col2 from '
+ '(select distinct col1 = ' +
CASE @intTemp
WHEN 3 THEN
CASE
WHEN @inyGrouping IN (1,3) THEN 'datename(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE CASE @inyGrouping
WHEN 2 THEN '''Week'
WHEN 4 THEN '''Quarter'
WHEN 5 THEN '''Year'
END + '_'' + ' + 'datename(' +
CASE @inyGrouping
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead)+ ')'
END
ELSE CASE @intColType
WHEN 1 THEN 'convert(varchar(255), ' + RTRIM(@chrColHead) + ')'
ELSE RTRIM(@chrColHead)
END
END + ',col2 = '+
CASE @intTemp
WHEN 3 THEN 'datepart(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 2 THEN 'week'
WHEN 3 THEN 'month'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE '0'
END + ',col3 = '+
CASE @intTemp
WHEN 3 THEN 'datepart(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE RTRIM(@chrColHead)
END + ' from ' +RTRIM(@chrSource) + ') xyz order by col3'
--PRINT @chvExec
EXEC(@chvExec)
/* Check column count */
IF (SELECT COUNT(*) FROM #colnames) > 1023
BEGIN
DROP TABLE #colnames
RAISERROR 51004 'Distinct column count exceeded max of 1023.'
RETURN -1
END
/* Verify colnames do not exceed max length */
IF (SELECT MAX(DATALENGTH(RTRIM(colname)) - 1) FROM #colnames) > 29
BEGIN
DROP TABLE #colnames
RAISERROR 51050 'Column data length exceeded max of 30.'
RETURN -1
END
/* If all is OK,continue to add #rownames data */
SELECT @chvExec = 'insert #rownames select distinct ' +
CASE @intRowType
WHEN 1 THEN 'convert(varchar(255), '
ELSE ''
END + RTRIM(@chrRowHead) +
CASE @intRowType
WHEN 1 THEN ')'
ELSE ''
END + ' from ' + @chrSource
--PRINT @chvExec
EXEC(@chvExec)
/*
Would be nice if you could use this value to define the crosstable
but this table must be created in a non-dynamic fashion.
*/
SELECT @intMaxRowHead=
(SELECT MAX(DATALENGTH(RTRIM(rowname))) FROM #rownames)
/* Create crosstable */
/* Define crosstable with rowhead field */
CREATE TABLE crosstable (rowhead varchar(255) NULL)
/* Alter crosstable by adding columns based on #colnames data */
DECLARE colname_cursor2 CURSOR FOR
SELECT colname FROM #colnames
OPEN colname_cursor2
FETCH colname_cursor2 INTO @chvCol
WHILE @@fetch_status >= 0
BEGIN
SELECT @chvColTemp = ''
IF @chvCol LIKE '%[^A-Z0-9]%'
BEGIN
SELECT @insR=1
WHILE @insR DATALENGTH(RTRIM(@chvCol))
BEGIN
SELECT @chvColTemp = RTRIM(@chvColTemp) +
CASE
WHEN SUBSTRING(@chvCol,@insR,1) LIKE '[A-Z0-9_]'
THEN SUBSTRING(@chvCol,@insR,1)