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

原作者: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

结果：

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-年份

     */

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

              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)

                       ELSE ''

                       END

                   SELECT @insR = @insR + 1

              END

              SELECT @chvCol = @chvColTemp

         END

         SELECT @chvExec = 'alter table ' + user_name() + '.crosstable add '+

              CASE

              WHEN SUBSTRING(@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol

              ELSE '_' + LTRIM(@chvCol)

              END + ' ' + @chvColType + ' null default(0)'

                  

              --PRINT @chvExec

              EXEC(@chvExec)

              FETCH colname_cursor2 INTO @chvCol

     END

    

     CLOSE colname_cursor2

     DEALLOCATE colname_cursor2

     /*   Add #rowhead data to crosstable */

    

     SELECT @chvExec='insert ' + USER_NAME() + '.crosstable (rowhead) select rowname from #rownames'

     --PRINT @chvExec

     EXEC(@chvExec)

    

     /*

         Create cursor with @chvRowHead and @chvColHead groupings and @chvValue

         aggregate

     */

    

     SELECT @chvExec = 'declare colname_cursor3 cursor for select ' +

         CASE @intRowType

         WHEN 1 THEN 'convert(varchar(255),' + RTRIM(@chrRowHead) + ')'

         ELSE RTRIM(@chrRowHead)

         END + ',' +

        

         CASE

         WHEN @intTemp = 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 + ',total = convert(varchar(255),' + RTRIM(@chvType) + '(' +

              RTRIM(@chrValue) + ')) from ' + RTRIM(@chrSource) + ' group by ' +

              RTRIM(@chrRowHead) + ',' +

             

         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 RTRIM(@chrColHead)

         END

        

     --PRINT @chvExec

     EXEC(@chvExec)

     /* Iterate through cursor and update crosstable */

    

     BEGIN TRAN

     OPEN colname_cursor3

     FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal

     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)

                       ELSE ''

                       END

                   SELECT @insR = @insR + 1

              END

              SELECT @chvCol = @chvColTemp

         END

         SELECT @chvExec = 'update ' + USER_NAME() + '.crosstable set ' +

          CASE

          WHEN SUBSTRING(@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol

          ELSE '_' + LTRIM(@chvCol)

          END + ' = ' +

              CASE

              WHEN @chvVal IS NULL THEN '0'

              ELSE RTRIM(@chvVal)

              END + ' where rowhead = '''

         SELECT @chvRow =

              CASE WHEN @chvRow IS NULL THEN 'NULL'

              ELSE RTRIM(@chvRow)

              END

         SELECT @chvRowTemp = ''

         IF @chvRow LIKE '%''%'

         BEGIN

              SELECT @insR = 1

              WHILE @insR <= DATALENGTH(RTRIM(@chvRow)) - 1

              BEGIN

                   SELECT @chvRowTemp = RTRIM(@chvRowTemp) +

                       CASE

                       WHEN SUBSTRING(@chvRow,@insR,1) LIKE '[^'']' THEN

                            SUBSTRING(@chvRow,@insR,1)

                       ELSE ''''''

                       END

                   SELECT @insR = @insR + 1

              END

              SELECT @chvRow = @chvRowTemp

         END

         SELECT @chvExec = @chvExec + @chvRow + ''''

         --PRINT @chvExec

         EXEC(@chvExec)

         FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal

     END

    

     CLOSE colname_cursor3

     DEALLOCATE colname_cursor3

     COMMIT TRAN

    

     /* Send back the data from crosstable */

    

     SET NOCOUNT OFF

    

     SELECT @chvExec = 'select * from ' + USER_NAME() + '.crosstable'

    

     --PRINT @chvExec

     EXEC(@chvExec)

    

     /* Drop the tables */

     DROP TABLE #colnames

     DROP TABLE #rownames

     DROP TABLE crosstable               

     RETURN

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

-----交叉表应用实例

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)

Go


---以下为交叉表的列数是确定的

select name,sum(case subject when '数学' then source else 0 end) as '数学',

sum(case subject when '英语' then source else 0 end) as '英语',

sum(case subject when '语文' then source else 0 end) as '语文'

from test
group by name

---以下为交叉表的列数是不确定的


declare @sql varchar(8000)

set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
print (@sql)
exec(@sql)

go