我现在有一张表。如下,
编号 年月 数量
A01 2009-01 100
A01 2009-02 150
A02 2009-01 300
A02 2009-02 350我现在想得到这样的一张结果表:编号 1月(2009-01) 2月(2009-02)
A001 100 150
A002 300 350 请问该如何实现啊,谢谢各位大侠了....分不够再加啊!!!!
编号 年月 数量
A01 2009-01 100
A01 2009-02 150
A02 2009-01 300
A02 2009-02 350我现在想得到这样的一张结果表:编号 1月(2009-01) 2月(2009-02)
A001 100 150
A002 300 350 请问该如何实现啊,谢谢各位大侠了....分不够再加啊!!!!
select 编号,
1月(2009-01)=isnull(sum(case course when '2009-01' then result end),0),
2月(2009-02)=isnull(sum(case course when '2009-02' then result end),0)
from 表
group by 编号
order by 编号
set @sql='select 编号'
select @sql=@sql+','+t.年月+'=isnull(sum(case 年月 when '''+t.年月+''' then 数量 end),0)'
from 表 group by 年月 order by 年月
set @sql=@sql+' from 表 t group by 编号 order by 编号'
print @sql
exec(@sql)
no cdate num
A01 2009-01 100
A01 2009-02 150
A02 2009-01 300
A02 2009-02 350
select no,sum(case m when 1 then total else 0 end) as '一月',sum(case m when 2 then total else 0 end) as '二月'
from
(select month(cdate) as m,no,sum(num) as total from test group by no,month(cdate) ) DERIVEDTBL
group by no
insert into #tbl
select 'A01','2009-01',100 union all
select
'A01','2009-02',150 union all
select
'A02','2009-01',300 union all
select
'A02','2009-02',350 declare @sql nvarchar(4000)
set @sql='select 编号'
select @sql=@sql+',isnull(sum(case 年月 when '''+年月+''' then 数量 else 0 end),0) as ['+substring(年月,7,1)+'月('+年月+')]'
from #tbl group by 年月 order by 年月
set @sql=@sql+' from #tbl group by 编号 order by 编号' exec(@sql)
drop table #tbl
看到你的答案后,使我对未来充满的信心!
(ID nvarchar(100),
Date nvarchar(100),
SumS int)insert T_Test values('A01','2009-01',100)
insert T_Test values('A01','2009-02', 150)
insert T_Test values('A02','2009-01',300)
insert T_Test values('A02','2009-02',350)
select ID,Y200901=sum(case when Date='2009-01' then SumS end),
Y200902=sum(case when Date='2009-02' then SumS end)
from T_Test group by ID