统计查询－－按某字段值动态生成列

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[CommonUseStatDynamicField](
@strTabName as varchar(50),           --表名
@strDynamicField as varchar(50),      --表头分组依据字段
@strGroup as varchar(50),             --分组字段
@strStatField as varchar(50),         --被统计的字段
@strOperation as varchar(10),         --运算方式
@EmptyIdentifier as Varchar(10),      --为空时的默认标识
@StrCondition as Varchar(100))        --where条件
AS

DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE DynamicFiledCursor CURSOR FOR SELECT DISTINCT ' + @strDynamicField + ' from ' + @strTabName + ' for read only ')
begin
  SET nocount ON
  SET @strsql ='select ' + @strGroup --+ ', ' + @strOperation + '(' + @strStatField + ') AS [' + @strOperation + 'Of' + @strStatField + ']' --查询的前半段

  OPEN DynamicFiledCursor
  while (0 = 0)
  BEGIN
    FETCH NEXT FROM DynamicFiledCursor   
    INTO @strTmpCol
    if (@@fetch_status <> 0) break
      SET @strsql = @strsql + ', ' + @strOperation + '(CASE ' + @strDynamicField + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strStatField + ' ELSE ' +  @EmptyIdentifier + ' END) AS [' + @strTmpCol + ' ' + @strDynamicField + ']' 
  END
  if @strCondition = ''
  set @strCondition = ' 1=1 '
  SET @strsql = @strsql + ' from ' + @strTabname + ' where ' + @strCondition + ' group by ' + @strGroup 

  EXECUTE(@strsql)

  IF @@error <>0 RETURN @@error        
  CLOSE DynamicFiledCursor
  DEALLOCATE DynamicFiledCursor RETURN 0
end