请教行转列
y m rm
201031
201052
201114
201125
201133
结果
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
set @sql=@sql+'from tb group by m'
exec(@sql)
select m,max(case when y=2010 then rm else 0 end) [2010],
max(case when y=2011 then rm else 0 end) [2010]
from tb group by m
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 m,'+@str+' from tb group by m'
exec(@str)
Ver.2005,2008,2008R2
Create Table #
(
yint,
mint,
rmint
)
Insert # Select 2010,3,1
union Select 2010,5,2
union Select 2011,1,4
union Select 2011,2,5
union Select 2011,3,3
Select * From #
Select m,ISNULL([2010],0) As [2010],ISNULL([2011],0) As [2011] From #
pivot(Sum(rm) for y in ([2010],[2011])) t
