SQL2005实现行转成列

/*参考 爱新觉罗.毓华

时间            值
2009-01-01      90
2009-01-02      99
2009-02-01      95

得到如下结果：
mycol  200901  200902
----- ------- -------
mycol  189      95
---------------------
*/

create table #mytb(time datetime,value float)
insert into #mytb values('2009-01-01',90)
insert into #mytb values('2009-01-05',80)
insert into #mytb values('2009-02-01',90)
insert into #mytb values('2009-03-01',88)
insert into #mytb values('2009-04-01',91)
insert into #mytb values('2009-05-01',92)
insert into #mytb values('2009-06-01',95)
insert into #mytb values('2009-07-01',97)
insert into #mytb values('2009-08-01',99)
insert into #mytb values('2009-09-01',86)
insert into #mytb values('2009-10-01',85)
insert into #mytb values('2009-11-01',88)
insert into #mytb values('2009-12-01',87)
insert into #mytb values('2009-12-01',80)
insert into #mytb values('2009-02-01',89)

--创建临时表
create table #tb(yyyymm varchar(6),value float,mycol varchar(5))
insert into #tb select year(time)*100+month(time),value,'mycol' from #mytb

--动态实现行转成列
declare @sql varchar(8000)
set @sql='select mycol '
select @sql=@sql+','+quotename(yyyymm)+'=sum(case yyyymm when '''+yyyymm+''' then value else 0 end)'
from (select distinct(yyyymm) from #tb) a
exec(@sql+' from #tb group by mycol')

--静态实现
select
sum(case when yyyymm='200901' then value else 0 end) '一月',
sum(case when yyyymm='200902' then value else 0 end) '二月',
sum(case when yyyymm='200903' then value else 0 end) '三月',
sum(case when yyyymm='200904' then value else 0 end) '四月',
sum(case when yyyymm='200905' then value else 0 end) '五月',
sum(case when yyyymm='200906' then value else 0 end) '六月',
sum(case when yyyymm='200907' then value else 0 end) '七月',
sum(case when yyyymm='200908' then value else 0 end) '八月',
sum(case when yyyymm='200909' then value else 0 end) '九月',
sum(case when yyyymm='200910' then value else 0 end) '十月',
sum(case when yyyymm='200911' then value else 0 end) '11月',
sum(case when yyyymm='200912' then value else 0 end) '12月'
from #tb

drop table #mytb
drop table #tb