水如烟

                 顺其自然,水到渠成 LzmTW

文或代码皆是面向初学者.我是爱好者,也是初学者.那些"文章",只按自己理解写,我是不知术语名词的.所以只供参考,也仅供参考.

导航

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

Posted on 2005-10-16 23:34  水如烟(LzmTW)  阅读(406)  评论(0编辑  收藏  举报

原作者: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(@chvTempNOT 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(@chvRowTypeNOT 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 > 0OR (@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(255NULL,colnumber int NULL
    
CREATE TABLE #rownames (rowname varchar(255NULL
         
    
/* 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,3THEN '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)) - 1FROM #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(255NULL
     
    
/* 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,1LIKE '[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,1LIKE '[^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,3THEN '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,3THEN '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,1LIKE '[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,1LIKE '[^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,1LIKE '[^'']' 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