create table a(ID varchar(10),[1] int,[2] int)
create table b(ItemID int,ItemName varchar(20))insert a select '00001',1000,2000
insert b select 1,'固定'
union all select 2,'变动'select ID,sum(case ItemName when '固定' then [1] end) as '固定'
,sum(case ItemName when '变动' then [2] end) as '变动' from a,b
group by IDID 固定 变动
---------- ----------- -----------
00001 1000 2000
create table b(ItemID int,ItemName varchar(20))insert a select '00001',1000,2000
insert b select 1,'固定'
union all select 2,'变动'select ID,sum(case ItemName when '固定' then [1] end) as '固定'
,sum(case ItemName when '变动' then [2] end) as '变动' from a,b
group by IDID 固定 变动
---------- ----------- -----------
00001 1000 2000
create table Test_Item_b(ItemID int,ItemName varchar(20))
insert into Test_Detail_a
select '00001',1000,2000
insert into Test_Item_b
select 1,'固定'
union all select 2,'变动'declare @sql varchar(2000)
set @sql = ''
select @sql=@sql+','+'['+name+'] as ['+ItemName+']'
from (select a.name,b.ItemName
from syscolumns a,Test_Item_b b
where a.name=b.ItemID and id=object_id('Test_Detail_a') and name<>'ID')t
exec('select ID'+@sql+' from Test_Detail_a')
/*
ID 固定 变动
---------- ----------- -----------
00001 1000 2000
*/
我的数据有很多的时候用case可能不是很现实。