有一张表，我想得到以下效果，该如何制作？

[84175971]

销售日期 分店 销售额
2002-1-1 A 50
2002-2-3 A 60
2002-1-8 B 70
2002-2-7 B 80
2002-1-17 A 30
2002-1-20 A 60
2002-2-25 A 1
2002-2-3 B 1
2002-2-4 C 61

经过处理后，得到以下表格
分店 [1月] [2月]
A 140 61
B 70 81
C 61
请问高手该如何处理

[feelingrun]

select a.分店，sum( a.销售额 * charindex('01',a.month)) as m01,
sum( a.销售额 * charindex('02',a.month)) as m02,
sum( a.销售额 * charindex('03',a.month)) as m03,
sum( a.销售额 * charindex('04',a.month)) as m04,
sum( a.销售额 * charindex('05',a.month)) as m05,
sum( a.销售额 * charindex('06',a.month)) as m06,
sum( a.销售额 * charindex('07',a.month)) as m07,
sum( a.销售额 * charindex('08',a.month)) as m08,
sum( a.销售额 * charindex('09',a.month)) as m09,
sum( a.销售额 * charindex('10',a.month)) as m10,
sum( a.销售额 * charindex('11',a.month)) as m11,
sum( a.销售额 * charindex('12',a.month)) as m12,

from （select 分店，month=case charindex('-',销售日期，6）
when 7 then '0'+substring(6,1)
when 8 then substirng(6,2)
end ,销售额 from 表）a

group by a.分店

[pengdali]

create table #1 (销售日期 datetime,分店 char(1),销售额 int)
insert #1 values('2002-1-1','A', 50)
insert #1 values('2002-2-3','A', 60)
insert #1 values('2002-1-8','B', 70)
insert #1 values('2002-2-7','B', 80)
insert #1 values('2002-1-17','A', 30)
insert #1 values('2002-1-20','A', 60)
insert #1 values('2002-2-25','A', 1)
insert #1 values('2002-2-3','B', 1)
insert #1 values('2002-2-4','C', 61)

declare @sql varchar(8000)
set @sql = 'select 分店,'

select @sql = @sql + 'sum(case month(销售日期) when '+cast(销售月 as varchar)+'
then 销售额 else 0 end) '''+cast(销售月 as varchar)+'月'','
from (select distinct month(销售日期) 销售月 from #1) as a

select @sql = left(@sql,len(@sql)-1) + ' from #1 where year(销售日期)=2002 group by 分店'

exec(@sql)
go

drop table #1

[84175971]

特别感谢大力给我多次的帮助，及各位的支持。

[宸瑜]

交叉表的问题，其实以前有过不少这样的贴子
feelingrun(神兵天将) 兄的也可以，不过几个地方可能写错了或遗漏了

select a.分店，sum( a.销售额 * charindex('01',a.month)) as m01,
sum( a.销售额 * charindex('02',a.month)) as m02,
sum( a.销售额 * charindex('03',a.month)) as m03,
sum( a.销售额 * charindex('04',a.month)) as m04,
sum( a.销售额 * charindex('05',a.month)) as m05,
sum( a.销售额 * charindex('06',a.month)) as m06,
sum( a.销售额 * charindex('07',a.month)) as m07,
sum( a.销售额 * charindex('08',a.month)) as m08,
sum( a.销售额 * charindex('09',a.month)) as m09,
sum( a.销售额 * charindex('10',a.month)) as m10,
sum( a.销售额 * charindex('11',a.month)) as m11,
sum( a.销售额 * charindex('12',a.month)) as m12,

from （select 分店，month=case charindex('-',销售日期，6）
when 7 then '0'+substring(month,6,1)
when 8 then substirng(month,6,2)
end ,销售额 from 表）a

group by a.分店

[宸瑜]

赞成pengdali(大力)兄

create table #1 (销售日期 datetime,分店 char(1),销售额 int)
insert #1 values(销售日期,分店,销售额)
select 销售日期,分店,销售额 from 表

declare @sql varchar(2000)
set @sql = 'select 分店'

select @sql = @sql + 'sum(case month(销售日期) when '+cast(销售月 as varchar)+'
then 销售额 else 0 end) '''+cast(销售月 as varchar)+'月'','
from (select distinct month(销售日期) 销售月 from #1) as a

select @sql = left(@sql,len(@sql)-1) + ' from #1 where year(销售日期)=2002 group by 分店'

exec(@sql)
go

drop table #1