declare @s varchar(8000)
set @s=''
select @s=@s+',['+dept+']=sum(case id when '''+bill_id+''' then num else 0 end)'
from bill_master
set @s=stuff(@s,1,1,'')
exec('
select id=0,* into #t from bill_details order by id
declare @id varchar(10),@i int
update #t set @i=case id when @id then @i+1 else 1 end
select '+@s+' from #t group by id')
set @s=''
select @s=@s+',['+dept+']=sum(case id when '''+bill_id+''' then num else 0 end)'
from bill_master
set @s=stuff(@s,1,1,'')
exec('
select id=0,* into #t from bill_details order by id
declare @id varchar(10),@i int
update #t set @i=case id when @id then @i+1 else 1 end
select '+@s+' from #t group by id')
set @s=''
select @s=@s+',['+dept+']=sum(case id when '''+bill_id+''' then num else 0 end)'
from bill_master a
where exists(
select * from bill_details where id=a.bill_id)
set @s=stuff(@s,1,1,'')
exec('
select gid=0,* into #t from bill_details order by id
declare @id varchar(10),@i int
update #t set @i=case id when @id then @i+1 else 1 end,gid=@i,@id=id
select * from #t
select '+@s+' from #t group by gid')
set @s=''
select @s=@s+',['+dept+']=sum(case id when '''+bill_id+''' then num else 0 end)'
from bill_master a
where exists(
select * from bill_details where id=a.bill_id)
set @s=stuff(@s,1,1,'')
exec('
select gid=0,* into #t from bill_details order by id
declare @id varchar(10),@i int
update #t set @i=case id when @id then @i+1 else 1 end,gid=@i,@id=id
select '+@s+' from #t group by gid')
create table bill_master(bill_id varchar(10),dept varchar(10))
insert bill_master select '001','生产部'
union all select '002','计划部'
union all select '003','销售部'
union all select '004','发展部'
union all select '005','采购部'create table bill_details(id varchar(10),num int)
insert bill_details select '001','10'
union all select '001','20'
union all select '001','30'
union all select '002','40'
union all select '002','50'
union all select '002','60'
union all select '003','70'
union all select '003','80'
union all select '003','90'
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+dept+']=sum(case id when '''+bill_id+''' then num else 0 end)'
from bill_master a
where exists(
select * from bill_details where id=a.bill_id)
set @s=stuff(@s,1,1,'')
exec('
select gid=0,* into #t from bill_details order by id
declare @id varchar(10),@i int
update #t set @i=case id when @id then @i+1 else 1 end,gid=@i,@id=id
select '+@s+' from #t group by gid')
go--删除测试
drop table bill_master,bill_details/*--测试结果生产部 计划部 销售部
----------- ----------- -----------
10 40 70
20 50 80
30 60 90--*/