一个通用的交叉制表存储过程

原作者:John Papa, Matthew Shepker  1999

整理: 水如烟(http://blog.csdn.net/lzmtw) 2005-5-1

示例:

Use pubs

GO

exec prCrosstab 'stor_id','ord_date','qty','sales',1,5

结果：

以下为生成脚本：

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 */