好垃圾本书中好有用的两个存储过程

来源：
SQL动态交叉表
http://blog.csdn.net/cjh47349038/archive/2008/11/12/3285082.aspx
5.2　交叉表决策分析方案   （书）
http://ajava.org/readbook/java/javafabd/9240.html
利用存储过程实现交叉表格式数据查询的一种通用方法 （建立临时表的，可学习，不实用）
http://www.cnblogs.com/pcbhyy/archive/2005/09/09/233362.html

1.
创建

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  procedure proc_across_table 
@TableName as varchar(50),       --生成交叉表依据的表名 
@NewColumn as varchar(50),       --生成表头依据的字段名 
@GroupColumn as varchar(50),     --分组依据的字段名 
@StatColumn as varchar(50),      --欲统计的字段名 
@Operator as varchar(10)         --统计的运算方式 
AS 
DECLARE @SQL as varchar(1000), @Column as varchar(50) --定义参数 
EXECUTE ('DECLARE cursor_new_column CURSOR FOR SELECT DISTINCT ' + @NewColumn + ' from ' + @TableName + ' for read only ') --定义游标 
begin 
  SET nocount ON  
  SET @SQL='select ' + @GroupColumn + ', ' + @Operator + '(' + @StatColumn + ') AS [' + @Operator + ' of ' + @StatColumn + ']' --定义SQL语句头 
  OPEN cursor_new_column 
  while (0=0) 
  BEGIN --遍历游标 
    FETCH NEXT FROM cursor_new_column INTO @Column --通过游标获取列头信息 
    if (@@fetch_status<>0) break 
        SET @SQL = @SQL + ', ' + @Operator + '(CASE ' + @NewColumn + ' WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE 0 END) AS [' + @Column + ']' --循环追加SQL语句 
  END 
SET @SQL = @SQL + ' from ' + @TableName + ' group by ' + @GroupColumn --定义SQL语句尾 
  EXECUTE(@SQL) --执行SQL语句 
  PRINT @SQL --输出SQL语句 
  IF @@error <>0 RETURN @@error --如果出错，则返回错误代码 
  CLOSE cursor_new_column --关闭游标 
  DEALLOCATE cursor_new_column RETURN 0 --释放游标，释放成功则返回0 
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

运行

exec   proc_across_table    'student1','subject','name','source','sum'
或
exec   proc_across_table    @TableName='student1',@NewColumn='subject',@GroupColumn='name',@StatColumn='source',@Operator='sum'  

结果



2.
创建

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure procedure_across_table_b

@TableName as varchar(50),              --生成交叉表依据的表名

@NewColumn as varchar(50),              --生成表头依据的字段名

@BeforeColumnName as varchar(50),      --定义动态列开始名称

@AfterColumnName as varchar(50),       --定义动态列结束名称

@GroupColumn as varchar(50),            --分组依据的字段名

@FirstColumnName as varchar(50),       --定义第一列名称

@StatColumn as varchar(50),             --欲统计的字段名

@SecondColumnName as varchar(50),      --定义第二列名称

@Operator as varchar(10)                 --统计的运算方式

AS

DECLARE @SQL as varchar(1000), @Column as varchar(50) --定义参数

EXECUTE ('DECLARE cursor_new_column CURSOR FOR SELECT DISTINCT ' + 

                @NewColumn + ' from ' + @TableName + ' for read only ') --定义游标

begin

  SET nocount ON 

  SET @SQL='select ' + @GroupColumn + ' as '+ @FirstColumnName + ', ' + @Operator + 

                '(' + @StatColumn + ') AS [' + @SecondColumnName + ']' --定义SQL语句头

  OPEN cursor_new_column

  while (0=0)

  BEGIN --遍历游标

    FETCH NEXT FROM cursor_new_column INTO @Column --通过游标获取列头信息

    if (@@fetch_status<>0) break

        SET @SQL = @SQL + ', ' + @Operator + '(CASE ' + @NewColumn + ' WHEN ''' + 

                @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + 

                @BeforeColumnName + @Column + @AfterColumnName + ']' --循环追加SQL语句

  END

  SET @SQL = @SQL + ' from ' + @TableName + 

                ' group by ' + @GroupColumn --定义SQL语句尾

  EXECUTE(@SQL) --执行SQL语句

  PRINT @SQL --输出SQL语句

  IF @@error <>0 RETURN @@error --如果出错，则返回错误代码

  CLOSE cursor_new_column --关闭游标

  DEALLOCATE cursor_new_column RETURN 0 --释放游标，释放成功则返回0

end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

运行

exec   procedure_across_table_b    'student1','subject','高中','科','name','姓名','source','科目数','count'

结果



/////////////////////////////////////////////////////
表中的数据