MSSQL将查询结果横向显示

表1
year_month,amount
200801		5
200802		6
200803		9	
200802		4

显示结果
YEAR	01	02	03	TOT
2008	5	0	0	5
2008	0	10	0	10	
2008	0	0	9	9	
2008	0	0	0	24


可能多个年，多个月

 

CREATE PROCEDURE testpp

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

create table testaa

(

year_month int,

amount int

)

insert into testaa  values (200801,5)

insert into testaa  values (200802,6)

insert into testaa  values (200803,9)

insert into testaa  values (200804,4)

 declare @sql nvarchar(4000)
 declare @s_st nvarchar(2)
 declare @s_y nvarchar(4)
 declare @s_m nvarchar(2)
 declare @s_name nvarchar(300)
 set @s_name=''
 set @sql='create table testaa2(year1 int'
 declare cc cursor for select substring(convert(nvarchar(6),year_month),5,2) from testaa group by substring(convert(nvarchar(6),year_month),5,2)

 open cc
 fetch next from cc into @s_st
 while @@FETCH_STATUS =0
 begin
   set @sql=@sql+',m'+@s_st+' int default 0'
   set @s_name=@s_name+'m'+@s_st+'+'
print @s_name
 FETCH NEXT FROM cc into @s_st
 end
 close cc
 DEALLOCATE cc
 set @sql=@sql+', tot int default 0)'
 print @sql
 exec sp_executesql @sql

 declare cc cursor for select substring(convert(nvarchar(6),year_month),1,4),substring(convert(nvarchar(6),year_month),5,2) from testaa group by year_month

 open cc
 fetch next from cc into @s_y,@s_m
 while @@FETCH_STATUS =0
 begin
   set @sql='insert into testaa2 (year1,m'+@s_m+') select convert(int,substring(convert(char(6),year_month),1,4)),sum(amount) from testaa where year_month='+@s_y+@s_m+' group by year_month'
   print @sql
   exec sp_executesql @sql
 FETCH NEXT FROM cc into @s_y,@s_m
 end
 close cc
 DEALLOCATE cc 

print @s_name
 set @sql='update testaa2 set tot='+substring(@s_name,1,len(@s_name)-1)
print @sql
 exec sp_executesql @sql
select * from testaa2
 drop table testaa2

END
GO