实现通用交叉表统计存储过程

目的：实现通用交叉表统计存储过程

    2.表说明

 

    1)表名     Try         1)结构     NewName varchar(200) 名称     Years varchar(200) 年份     Counts int 数量         2）数据     NewName (表列标头） Years (表列标头） Counts (表列标头）         A 2001 1     B 2001 1     C 2002 1     d 2003 1     e 2004 1         3.执行结果             NewName 2001 2002 2003 2004     A 1     B 1     C 1     D 1     e 1         4.执行语句    

SELECT NewName, SUM(CASE Years WHEN '2001' THEN Counts ELSE NULL END) as '2001', SUM(CASE Years WHEN '2002' THEN Counts ELSE NULL END) as '2002', SUM(CASE Years WHEN '2003' THEN Counts ELSE NULL END) as '2002', SUM(CASE Years WHEN '2004' THEN Counts ELSE NULL END) as '2004' FROM Try GROUP BY NewName

 

        5.存储过程说明     1)调用方法     在查询分析器中输入    

exec Wind 'NewName','Years','Counts',' from Try group by NewName','Try'

 

        2)系统函数     1)系统函数 sp_executesql 执行动态生成的 Sql 语句，在存储过程 22 行         2) DECLARE abc CURSOR For ... 为调用游标语法         2)存储过程代码        

CREATE PROCEDURE Wind (@FirstColumn varchar(30) ,@NewColumn varchar(30) , @SumColumn varchar(30),@Other varchar(30) ,@TablerName varchar(30)) as

DECLARE @SelectSring nvarchar(200)

DECLARE @ColumnName nvarchar(200)

DECLARE @Datas varchar(200)

DECLARE @hh nvarchar(200)

DECLARE @bbb nvarchar(200)

 

DECLARE abc CURSOR FOR SELECT DISTINCT years FROM try OPEN abc

FETCH NEXT FROM abc into @Datas while @@fetch_status = 0

begin set @ColumnName = @ColumnName ",sum(case "+ @NewColumn + " when '" + @Datas + "' then " + @SumColumn + " else null end) as '" + @Datas + "'" fetch next from abc into @Datas end

close abc deallocate abc

set @hh = 'select ' + @FirstColumn + ' ' + @ColumnName + ' from ' + @TablerName + @Other exec sp_executesql @hh GO