sqlserver 数据横向查询和游标使用

USE [tempdb]
GO
/****** 对象:  StoredProcedure [dbo].[sp_sum]    脚本日期: 04/26/2013 14:36:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_sum]
 -- Add the parameters for the stored procedure here

AS
 declare @iii int
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 -- SET NOCOUNT ON
 
    -- Insert statements for procedure here
 --第一步：把数据纳入到表tb_jhsy 手工从excle插入到表
 --第二步:把数据插入到表tb_sum
 delete from tb_sum
 declare @result VARCHAR(30)
 Declare curSF Cursor for  
    select distinct xs from tb_jhsy;---查询语句（查询所有用户存储过程名称） 
 Open curSF  
  --循环并提取记录  
  Fetch Next From curSF Into @result--取第一条记录存入@result中  
  While ( @@Fetch_Status=0 )    
        begin 
   insert into tb_sum(dz,cb,qy,yf,yhyf)
    select @result,b.cb,sum(b.qy),sum(b.yf),sum(b.yfy) from (
    select a.cb,count(a.cb) as qy,0 as yf,0 as yfy from tb_jhsy a
    where a.xs = @result  group by a.cb
    union all
    select a.cb,0 as qy,count(a.cb) as yf,0 as yfy from tb_jhsy a
    where a.xs = @result and a.lb like '全员育妇%' group by a.cb
    union all
    select a.cb,0 as qy,0 as yf,count(a.cb) as yfy from tb_jhsy a
    where a.xs = @result and a.lb like '全员育妇已%' group by a.cb
    ) as b
    group by b.cb
           Fetch Next From curSF into @result ----下一条  
        end  
  --关闭游标     
   Close curSF 
  --释放游标  
 Deallocate curSF  
 --第三步:统计出需要的数据
    declare @s varchar(8000)
 set @s='select cb'
 select @s=@s+',['+ltrim(rtrim(dz))+'qy]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then qy else 0 end)'+',['+ltrim(rtrim(dz))+'yf]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then yf else 0 end)'+',['+ltrim(rtrim(dz))+'yhyf]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then yhyf else 0 end )'
 from tb_sum
 group by dz
 set @s=@s+' from tb_sum group by cb '
 exec(@s)

END