|
|
Posted on 2005-10-16 23:34 水如烟(LzmTW) 阅读(126) 评论(0) 编辑 收藏 网摘 所属分类: z、综合使用
原作者:John Papa, Matthew Shepker 1999 整理: 水如烟
示例:
 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)
日期: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 < 1 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 < 100
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 < @intTemp
BEGIN
RAISERROR 51020 'Crosstab type not valid with @chrValue definition.'
RETURN -1
END
/**//* Hold the data 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 < 0 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 < 100
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 < 100
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
&nb |