declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when office='''+office+''' then fact_qty else 0 end) as '''+office+''''
from 表1 group by office
select @sql='select procode'+@sql+' from 表1 group by procode'
exec(@sql)
set @sql=''
select @sql=@sql+',sum(case when office='''+office+''' then fact_qty else 0 end) as '''+office+''''
from 表1 group by office
select @sql='select procode'+@sql+' from 表1 group by procode'
exec(@sql)
(
office varchar(10),
procode varchar(10),
fact_qty int
)
insert A
select 'bj','p1',3 union
select 'bj','p2',4 union
select 'bj','p3',8 union
select 'sh','p1',2 union
select 'sh','p2',5 union
select 'sh','p3',9 --查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when office='''+office+''' then fact_qty else 0 end) as '''+office+''''
from A group by office
select @sql='select procode'+@sql+' from A group by procode'
exec(@sql)--删除测试环境
drop table A--结果
/*
procode bj sh
---------- ----------- -----------
p1 3.00 2.00
p2 4.00 5.00
p3 8.00 9.00
*/
set @s=''
select @s=@s+',['+office+']=sum(case office when '''+office+''' then factqty else 0 end)'
from 表 group by office
exec('select procode+@s+' from 表 group by procode')
set @s=''
select @s=@s+',['+office+']=sum(case office when '''+office+''' then fact_qty else 0 end)'
from 表1 group by office
exec('select procode+@s+' from 表1 group by procode')