--测试数据
create table 表([Month] int,F1 int,F2 int,F3 int,F4 int,F5 int)
insert 表 select 200401,400,300,200,600,800
union all select 200402,300,800,600,400,700
go
--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''[Fid]='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([Month] as varchar)+'']=''+cast(['+name+'] as varchar)'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'Month'
select a.fld,a.[200401],b.[200402] from (
select 'f1' as Fld,f1 as [200401] from 资料库 where Month='200401'
select 'f2' as Fld,f2 as [200401] from 资料库 where Month='200401'
select 'f3' as Fld,f3 as [200401] from 资料库 where Month='200401'
select 'f4' as Fld,f4 as [200401] from 资料库 where Month='200401'
select 'f5' as Fld,f5 as [200401] from 资料库 where Month='200401') a,
(
select 'f1' as Fld,f1 as [200402] from 资料库 where Month='200402'
select 'f2' as Fld,f2 as [200402] from 资料库 where Month='200402'
select 'f3' as Fld,f3 as [200402] from 资料库 where Month='200402'
select 'f4' as Fld,f4 as [200402] from 资料库 where Month='200402'
select 'f5' as Fld,f5 as [200402] from 资料库 where Month='200402') b
where a.fld=b.fld
select @1=@1+',['+cast(month as varchar)+']='+cast(f1 as varchar)
,@2=@2+',['+cast(month as varchar)+']='+cast(f2 as varchar)
,@3=@3+',['+cast(month as varchar)+']='+cast(f3 as varchar)
,@4=@4+',['+cast(month as varchar)+']='+cast(f4 as varchar)
,@5=@5+',['+cast(month as varchar)+']='+cast(f5 as varchar)
from 表
exec('select '+@1+' union all '+'select '+@2+' union all '+'select '+@3+' union all '+'select '+@4+' union all '+'select '+@5)