Unpivot列传行的应用

-- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
--  [weixin_health_screen_disease] 37385  
-- =============================================  
CREATE PROCEDURE [dbo].[weixin_health_screen_disease]  
 @kid int  
AS  
BEGIN  
 SET NOCOUNT ON;  
 /*  
 declare @bgndate varchar(10), @enddate varchar(10), @year int  
   
 if  MONTH(GETDATE()) >= 9  
     begin  
     set @year= (select CAST( YEAR(GETDATE()) as int))  
     set @bgndate = (select CAST( YEAR(GETDATE()) as varchar) + '-09-01')  
     set @enddate = (select CAST( YEAR(DATEADD(yy,1,GETDATE())) as varchar) + '-07-01')  
     end  
   else   
        begin   
         set @year= (select CAST( YEAR(DATEADD(yy,-1,GETDATE())) as varchar))  
        set @bgndate = (select CAST( YEAR(DATEADD(yy,-1,GETDATE())) as varchar) + '-09-01')  
        set @enddate = (select CAST( YEAR(GETDATE()) as varchar) + '-07-01')  
        end   
          
     select  ID,bgndate, LEFT( CONVERT(varchar(10),bgndate,120),7) bgnmonth ,disease  
       into #t  
      from BasicData..weixin_doctor_disease_record  
     where kid = @kid  
       and bgndate >= @bgndate  
       and bgndate < @enddate      
        
      select 0 code, '获取成功' info    
       
      create table #t1 (bgnmonth varchar(10),cnt int)  
        
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-09', COUNT(ID) cnt from #t t where bgnmonth = cast(@year as varchar(4)) + '-09'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-10', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-10'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-11', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-11'   
     insert into #t1(bgnmonth,cnt) select cast(@year as varchar(4)) + '-12', COUNT(ID) cnt from #t t where bgnmonth =  cast(@year as varchar(4)) + '-12'    
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-01' , COUNT(ID) cnt from #t t where bgnmonth =  cast((@year+1) as varchar(4)) + '-01'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-02' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-02'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-03' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-03'  
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-04' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-04'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-05' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-05'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-06' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-06'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-07' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-07'   
     insert into #t1(bgnmonth,cnt) select cast((@year+1) as varchar(4)) + '-08' , COUNT(ID) cnt from #t t where bgnmonth = cast((@year+1) as varchar(4)) + '-08'   
       
  
       
      select  bgnmonth,cnt  
      from #t1  
      order by bgnmonth  
        
      select t.bgnmonth, COUNT(ID) cnt  
      from  
      #t t   
      group by  t.bgnmonth   
      order by t.bgnmonth   
       
      select t.disease, COUNT(ID) cnt  
      from  
      #t t   
      group by  t.disease  
      order by t.disease  
       */  
  
  select 0 code, '获取成功' info  
  
  Declare @springday datetime  
  Select @springday = sdate From BasicData.dbo.Springday Where term = Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-0'  
  
  Select Convert(Varchar(7), cdate, 120) bgnmonth, disease, cnt  
    Into #t  
    From mcapp..record_mc_kid_day  
    Unpivot(cnt for disease In (fs, ks, hlfy, lbt, pz, fx, hy)) a  
    Where kid = @kid   
    and cdate >= Case When GETDATE() < @springday Then Cast(DATEPART(yy, GETDATE()) - 1 as Varchar(10)) + '-09-01'  
                        When GETDATE() >= Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-09-01' Then Cast(DATEPART(yy, GETDATE()) as Varchar(10)) + '-09-01'  
                        Else @springday End  
      and cdate < GETDATE()  
  
  Select bgnmonth, Sum(cnt) cnt  
    From #t  
    Group by bgnmonth  
    
  Select Case disease When 'fs' Then '发烧' When 'ks' Then '咳嗽' When 'hlfy' Then '喉咙发炎' When 'lbt' Then '流鼻涕'   
                      When 'pz' Then '皮疹' When 'fx' Then '腹泻' When 'hy' Then '红眼病' Else '其它' End disease, Sum(cnt) cnt  
    From #t  
    Group by disease  
  
END