交叉统计报表存储过程的实现

用了2个小时写的下面的过程,供大家学习与交流,并且测试通过,不过代码写的很冗余.大家自己优化一下就OK了

下面的存储过程是根据具体的要求来写的,你也可以根据自己的要求重新写,如果你对各种复杂的交叉报表有更好的解决办法,我们交流一下

说明:代码中的行类别和列部门是事先在数据库中维护好的,员工信息必须有该员工的所在部门和所属类别字段,才能应用此过程

/*
名称：员工类别交叉统计报表
功能：根据行类别与员工所在部门交叉统计出人数个数，每次统计的结果保存在MANPOWER_SORT_TEMP表
编码：LI-CHANG-HAO E-MAIL:LICHANGHAO2008@GMAIL.COM
时间: 2007-05-17
参数：@ROW_SORT-传入行类别参数以逗号分割的字符串;@COL_SORT-传入列部门参数以逗号分割的字符串
说明：此功能只对单一的行和列进行交叉，对复杂的行表头不支持

版本：V1.0
*/
--EXEC EMPLOYEE_SORT_STATISTIC '002,0021,011,0022,017,007,015','100,10009,10610,101,102,103,104,105,106,888,405'
--SELECT * FROM MANPOWER_SORT_TEMP
CREATE PROC EMPLOYEE_SORT_STATISTIC
(
   @ROW_SORT NVARCHAR(4000),
   @COL_SORT NVARHCAR(4000)
)
AS
DECLARE @ROW_SORT NVARCHAR (4000)
DECLARE @COL_SORT NVARCHAR (4000)
DECLARE @SQLSTR_ROW NVARCHAR (4000)
DECLARE @SQLSTR_COL NVARCHAR (4000)
DECLARE @EXECSQLSTR NVARCHAR (4000)
DECLARE @CREATETABLE_STR NVARCHAR (4000)
DECLARE @SEPARATOR CHAR(1)
DECLARE @I INT
DECLARE @ROW_SORTLENGTH INT,@SEPARATORINDEX INT,@BEGININDEX INT,@COL_SORTLENGTH INT
--SET @ROW_SORT='002,0021,011'
--SET @COL_SORT='100,10009,10610'
SET @SEPARATOR=','
SET @SQLSTR_ROW=''
SET @SQLSTR_COL=''
SET @EXECSQLSTR=''
SET @ROW_SORTLENGTH=LEN(@ROW_SORT)
SET @COL_SORTLENGTH=LEN(@COL_SORT)
SET @BEGININDEX=1

CREATE TABLE #ROW_SORT
(
   ID INT IDENTITY(1,1),
   ROWVAL NVARCHAR(100)
)

CREATE TABLE #COL_SORT
(
   ID INT IDENTITY(1,1),
   COLVAL NVARCHAR(100)
)
SET @I=1
SET @CREATETABLE_STR='CREATE TABLE MANPOWER_SORT_TEMP ( LCH0 NVARCHAR(100) , LCH1 NVARCHAR(100),'--根据行类别动态生成表结构
WHILE @BEGININDEX <= @ROW_SORTLENGTH--行类别批分保存
  BEGIN
     SET @I=@I + 1
     SELECT @SEPARATORINDEX = CHARINDEX(@SEPARATOR,@ROW_SORT,@BEGININDEX) 
     DECLARE @ROW_CHAR NVARCHAR(4000)
     IF @SEPARATORINDEX = 0
         SELECT @SEPARATORINDEX = @ROW_SORTLENGTH + 1
      SELECT @ROW_CHAR = SUBSTRING(@ROW_SORT,@BEGININDEX,@SEPARATORINDEX-@BEGININDEX)
      INSERT INTO #ROW_SORT VALUES (@ROW_CHAR)
      SET @CREATETABLE_STR=@CREATETABLE_STR + ' LCH'+CAST(@I AS NVARCHAR(20))+ '  NVARCHAR(100),'
      SELECT @BEGININDEX = @SEPARATORINDEX + 1
  END
SET @CREATETABLE_STR=LEFT(@CREATETABLE_STR,LEN(@CREATETABLE_STR)-1)
SET @CREATETABLE_STR=@CREATETABLE_STR + ')'

SET @BEGININDEX=1

WHILE @BEGININDEX <= @COL_SORTLENGTH--列部门批分保存
  BEGIN
     SELECT @SEPARATORINDEX = CHARINDEX(@SEPARATOR,@COL_SORT,@BEGININDEX) 
     DECLARE @COL_CHAR NVARCHAR(4000)
     IF @SEPARATORINDEX = 0
         SELECT @SEPARATORINDEX = @COL_SORTLENGTH + 1
      SELECT @COL_CHAR = SUBSTRING(@COL_SORT,@BEGININDEX,@SEPARATORINDEX-@BEGININDEX)
      INSERT INTO #COL_SORT VALUES (@COL_CHAR)
      SELECT @BEGININDEX = @SEPARATORINDEX + 1
  END
 
  BEGIN TRAN
  --BEGIN 计算出报表行的表头信息------------------------------------------------------
 
 DECLARE @ID_ROW INT
 DECLARE @ROWVAL NVARCHAR(100)
        DECLARE @ROWFORMAT NVARCHAR(100)
        DECLARE MYROW_SORT CURSOR FOR                         
         SELECT ID,ROWVAL FROM #ROW_SORT                  
         OPEN MYROW_SORT                          
         FETCH NEXT FROM MYROW_SORT INTO @ID_ROW,@ROWVAL   
         WHILE  @@FETCH_STATUS=0                    
          BEGIN
                            SET @ROWFORMAT=''
                            SELECT @ROWFORMAT=R0303 FROM R03 WHERE R0302=@ROWVAL
                            IF(@SQLSTR_ROW='')
                 SET @SQLSTR_ROW=''''+@ROWFORMAT+'''' 
                            ELSE                            
                 SET @SQLSTR_ROW=@SQLSTR_ROW + ','''+@ROWFORMAT+''''               
              FETCH NEXT FROM MYROW_SORT INTO @ID_ROW,@ROWVAL
          END
  CLOSE MYROW_SORT                              
  DEALLOCATE MYROW_SORT
  --END------------------------------------------------------------------------------   

  --BEGIN 计算出报表列的部门信息------------------------------------------------------------
         DECLARE @ID_COL INT
  DECLARE @COLVAL NVARCHAR(100)
         DECLARE @COLFORMAT NVARCHAR(100)
         DECLARE @SUM_COL NVARCHAR(100)
         DECLARE @SUM_COL_ROW NVARCHAR(100)
         DECLARE @TEMP1 NVARCHAR(100)
         DECLARE @TEMP2 NVARCHAR(100)
         DECLARE MYCOL_SORT CURSOR FOR                         
          SELECT ID,COLVAL FROM #COL_SORT                  
          OPEN MYCOL_SORT
                 FETCH NEXT FROM MYCOL_SORT INTO @ID_COL,@COLVAL   
          WHILE  @@FETCH_STATUS=0                    
          BEGIN
                            SET @COLFORMAT=''
                            SET @SUM_COL=''
                            SET @TEMP1=''
                            SET @TEMP2=''
                            SELECT @COLFORMAT=A0503 FROM A05 WHERE A0502=@COLVAL
                            SELECT @SUM_COL=COUNT(*) FROM R01 WHERE R0102=@COLVAL AND R0138='是'
                            IF(@SUM_COL=0)
                               SET @SUM_COL=''
                             WHILE EXISTS(SELECT TOP 1 ROWVAL  FROM #ROW_SORT)
               BEGIN
                    SELECT TOP 1 @TEMP1=ROWVAL FROM #ROW_SORT
                                         SELECT @SUM_COL_ROW=COUNT(*) FROM R01 WHERE R0102=@COLVAL AND  ','+R0137+',' LIKE '%,'+@TEMP1+',%' AND R0138='是'
                                         IF(@SUM_COL_ROW=0)
                                            SET @SUM_COL_ROW=''
                                         SET @TEMP2=@TEMP2 + ',' + ''''+@SUM_COL_ROW+''''
                                         DELETE FROM  #ROW_SORT WHERE ROWVAL=@TEMP1
               END
                            SET @BEGININDEX=1
                            WHILE @BEGININDEX <= @ROW_SORTLENGTH--再次导入行类别进行批分
      BEGIN
         SELECT @SEPARATORINDEX = CHARINDEX(@SEPARATOR,@ROW_SORT,@BEGININDEX) 
         DECLARE @ROW_CHAR_TEMP NVARCHAR(4000)
         IF @SEPARATORINDEX = 0
             SELECT @SEPARATORINDEX = @ROW_SORTLENGTH + 1
          SELECT @ROW_CHAR_TEMP = SUBSTRING(@ROW_SORT,@BEGININDEX,@SEPARATORINDEX-@BEGININDEX)
          INSERT INTO #ROW_SORT VALUES (@ROW_CHAR_TEMP)
          SELECT @BEGININDEX = @SEPARATORINDEX + 1
      END
                            IF(@SQLSTR_COL='')
                 SET @SQLSTR_COL=' UNION ALL SELECT '''+@COLFORMAT+''','''+@SUM_COL+'''' + @TEMP2
                            ELSE                            
                 SET @SQLSTR_COL=@SQLSTR_COL + ' UNION ALL SELECT '''+@COLFORMAT+''','''+@SUM_COL+'''' + @TEMP2
              FETCH NEXT FROM MYCOL_SORT INTO @ID_COL,@COLVAL
          END
  CLOSE MYCOL_SORT                              
  DEALLOCATE MYCOL_SORT
  --END--------------------------------------------------------------------------------
  COMMIT TRAN
 
SET @EXECSQLSTR='SELECT '''',''部门人数'','+@SQLSTR_ROW+''--报表头行信息对接
SET @EXECSQLSTR=@EXECSQLSTR+ @SQLSTR_COL--采用UNION ALL 组合行信息
DROP TABLE #ROW_SORT
DROP TABLE #COL_SORT
IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[MANPOWER_SORT_TEMP]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[MANPOWER_SORT_TEMP]
EXEC(@CREATETABLE_STR)--根据行类别个数动态创建检索表
DECLARE @EXECSTR NVARCHAR(4000)
SET @EXECSTR='INSERT INTO MANPOWER_SORT_TEMP  ' + @EXECSQLSTR--将统计报表存入检索表
EXEC(@EXECSTR) 

效果如下图: