请教一个按月份动态将促销额行转列并且求移动季平均的问题
数据表结构如下：
年月 产品 促销额
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+ '01 ',b.Date+ '01 ')=1
and
datediff(month,a.Date+ '01 ',c.Date+ '01 ')=2) t
set @sql= 'select Product '+@sql+ ' from (
select
isnull(a.date, ' ' ' ')+ ' '+ ' '+
isnull(b.date,convert(char(6),dateadd(m,1,a.date+ ' '01 ' '),112))+ ' '+ ' '+
isnull(c.date,convert(char(6),dateadd(m,2,a.date+ ' '01 ' '),112)) as date,
a.Product,
(isnull(a.num,0)+isnull(b.num,0)+isnull(c.num,0))/3 as num
from
test a
left join
test b
on
a.Product=b.Product and datediff(month,a.Date+ ' '01 ' ',b.Date+ ' '01 ' ')=1
left join
test c
on
a.Product=c.Product and datediff(month,a.Date+ ' '01 ' ',c.Date+ ' '01 ' ')=2
where
a.Date not in(select distinct top 2 Date from test order by Date desc)) t
group by Product '
exec(@sql)
go
drop table test
go
