最近都在寫SQL SERVER的STORED PROCEDURE
還是新手
問題特別多 :)    假如我的RECORD長成這樣
NO DATE QTY
-- ---- ---
1  9301 100 
1  9302 200
1  9303 300
1  9304 500
2  9301 200 
2  9302 200
2  9303 300
2  9304 200
要SELECT成每個人一年內分成每兩個月的合計
像這樣
NO 1-2月 3-4月 5-6月 7-8月 9-10月 11-12月
-- ----- ----- ----- ----- ------ -------
1  300   800   0     0     0      0
2  400   500   0     0     0      0    該如何做呢?

//Create 一個 TmpTable    Create Table #Report 
(No int,
 Month12 numeric(20,5),
 Month34 numeric(20,5), 
 Month56 numeric(20,5),
 Month78 numeric(20,5),
 Month910 numeric(20,5),
 Month1112 numeric(20,5)
)    //宣告變數    declare @NO int
declare @QTY numeric(20,5)
declare @YEAR int
declare @Month int
declare @STRDATESTART char(4)
declare @STRDATEEND char(4)    //選擇條件年月跟組合成@STRDATE->'YYMM'
//自己寫吧    declare #c1 cursor FAST_FORWARD for
      select distinct No from RECORD
   open #c1
   fetch next from #c1 into @NO
   while @@fetch_status = 0
   begin
     //1,2月
     insert into #Report (No,Month12,Month34,Month56,Month78,Month910,Month1112) values(@NO,0,0,0,0,0,0)
     set @STRDATESTART='9301'
     set @STRDATEEND='9302'
     Set @QTY=(select sum(QTY) from RECORD where NO=@NO and DATE between @STRDATESTART and @STRDATEEND
     
     update RECORD set Month12=@QTY where NO=@NO
     
     //3,4月..以下省略跟1,2月一樣作法
     
   fetch next from #c1 into @NO
   end
   close #c1
   deallocate #c1
   
select * from  #Report      

 
select no_,sum(dt01) '1-2月' ,sum(dt02) '3-4月',sum(dt03) '5-6月',sum(dt04) '7-8月',sum(dt05) '9-10月', sum(dt06) '11-12月'
   from ( select no_,
                        case when (date_='9301') or date_='9302' then sum(qty_) else 0 end dt01,
                        case when (date_='9303') or date_='9304' then sum(qty_) else 0 end dt02,
                        case when (date_='9305') or date_='9306' then sum(qty_) else 0 end dt03,
                        case when (date_='9307') or date_='9308' then sum(qty_) else 0 end dt04,
                        case when (date_='9309') or date_='9310' then sum(qty_) else 0 end dt05,
                        case when (date_='9311') or date_='9312' then sum(qty_) else 0 end dt06
                 from aa1 
             group by no_,date_)a
group by no_
order by no_