y m rm
2010 3 1
2010 5 2
2011 1 4
2011 2 5
2011 3 3
结果
m 2010 2011
1 0 4
2 0 5
3 1 3
5 2 0
m即月份，y即年
请大家帮个忙，谢谢！
------最佳解决方案--------------------
create table tb(rm int,m int,y int)
insert tb
select 1,3,2010
union all
select 2,5,2010
union all
select 4,1,2011
union all
select 5,2,2011
union all
select 3,3,2011
drop table tb
declare @str varchar(1000)
set @str=''
select @str=@str+','+'max(case when y='+RTRIM(y)+' then rm else 0 end)['+RTRIM(y)+']' from tb group by y
set @str='select number+1 as m '+@str+' from master..spt_values a left join tb b on a.number+1=b.m
where type=''p'' and number<12 group by number+1'
exec(@str)
/*
m 2010 2011
----------- ----------- -----------
1 0 4
2 0 5
3 1 3
4 0 0
5 2 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0
------其他解决方案--------------------
create table tb(rm varchar(50),m varchar(50),year varchar(50))
insert tb
select '1','3','2010'
union all
select '2','5','2010'
union all
select '4','1','2011'
union all
select '5','2','2011'
union all
select '3','3','2011'
declare @sql varchar(max)
set @sql ='select m'
select @sql=@sql+', max(case year when '''+year+''' then rm else ''0'' end)['+year+']' from(select distinct year from tb)u