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

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