请教一个按月份动态将促销额行转列并且求移动季平均的问题
数据表结构如下：
年月             产品           促销额  
200601           A                 1000
200601           B                 2200
200602           A                 1500
.....           ....               ....

现在将产品按月份汇总后行这变成
产品     （1月+2月+3月）/3     （2月+3月+4月）/3   ...（10月+11月+12月）/3
  A                     5000                             4000                                 5000
  B                     6000                             3000                                 2000
...                   ....                             ....                                 ....  

月份是要随着时间不断增加的，解决后马上结贴，在线等

------解决方案--------------------
--最笨的办法是
select 产品
,sum(case when right(年月) in ( '01 ', '02 ', '03 ') then 促销额 else 0 end)/3
,sum(case when right(年月) in ( '02 ', '02 ', '04 ') then 促销额 else 0 end)/3
……
,sum(case when right(年月) in ( '10 ', '11 ', '12 ') then 促销额 else 0 end)/3
from t
group by 产品,left(年月,4)
------解决方案--------------------
create table test(Date varchar(16),Product varchar(10),num int)
insert into test select '200601 ', 'A ',1000
insert into test select '200601 ', 'B ',2200
insert into test select '200602 ', 'A ',1500
insert into test select '200603 ', 'A ',1600
insert into test select '200604 ', 'A ',1700
insert into test select '200605 ', 'A ',1800
insert into test select '200606 ', 'A ',1900
insert into test select '200607 ', 'A ',1000
insert into test select '200608 ', 'A ',1100
insert into test select '200609 ', 'A ',1200
insert into test select '200610 ', 'A ',1300
insert into test select '200611 ', 'A ',1400
insert into test select '200612 ', 'A ',1500
insert into test select '200701 ', 'A ',1600
go

declare @sql varchar(8000)
set @sql= ' '

select
@sql=@sql+ ',[( '+t.date+ ')/3]=sum(case date when ' ' '+t.date+ ' ' ' then num else 0 end) '
from
(select
a.date+ '+ '+b.date+ '+ '+c.date as date
from
(select distinct date from test) a,
(select distinct date from test) b,
(select distinct date from test) c
where
datediff(month,a.Date+ &#