逐月对比的交叉表处理呢

--测试数据

create table tb(year int,month int,No varchar(10),Name varchar(10),部门 varchar(10),工资 int)

insert tb select 2004,10,\'A001\',\'AAA\',\'DDD\',1000

union all select 2004,10,\'B001\',\'BBB\',\'DDD\',800

union all select 2004,11,\'A001\',\'AAA\',\'DDD\',1100

union all select 2004,11,\'B001\',\'BBB\',\'DDD\',1000

union all select 2004,12,\'A001\',\'AAA\',\'DDD\',1200

union all select 2004,12,\'B001\',\'BBB\',\'DDD\',1050

/*--处理要求

将月份做为字段,并且反应逐月的工资增幅,例如上面的数据要求结果如下,注意最小的年月是没有增幅的

No Name 部门 2004_10 2004_11 2004_11增幅 2004_12 2004_12增幅

------ ------ ------ --------- -------- ----------- --------- ------------

A001 AAA DDD 1000 1100 10.00% 1200 9.09%

B001 BBB DDD 800 1000 25.00% 1050 5.00%

--*/

go

--查询处理

declare @s nvarchar(4000),@i int

select @s=\'\',@i=0

select @s=@s+\',\'+quotename(fd)

+\'=sum(case when ar=\'+year

+\' and nth=\'+month

+\' then a.工资 end)\'

+case @i when 0 then \'\'

else \',\'+quotename(fd+\'增幅\')

+\'=cast(cast(sum(case when ar=\'+year

XP系统不能如期寿终正寝 +\' and nth=\'+month

+\' then a.工资-b.工资 end)*100.\'

+\'/sum(case when ar=\'+year

+\' and nth=\'+month

+\' then b.工资 end)\'

+\' as decimal(10,2)) as varchar)+\'\'%\'\'\'

end,@i=@i+1

from(

select year=rtrim(year),month=rtrim(month),

fd=rtrim(year)+\'_\'+rtrim(month)

from tb group by year,month)a

exec(\'select ,,a.部门\'+@s+\'

from tb a

left join tb b on = and = and a.部门=b.部门

and ar=ar+(nth)/12

and nth=nth%12+1

group by ,,a.部门\')

go

--删除测试

drop table tb