--1.生成列表的sql语句
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+']'
from 数据表 order by Fid
exec('
select 类别 ,货号,单价'+@s+',
在途库存, 办事处销售,办事处库存,
东北销售,东北库存,总仓销售,总仓库存,全国总计销售,全国总计库存
into #kd
from ##ryk
')
--2.生成汇总处理的
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+']=sum(['+Fname+'])'
from 数据表 order by Fid
exec('
select 类别+''小计'','''',avg(单价*0)'+@s+',
sum(在途库存),sum(办事处销售),sum(办事处库存),sum(东北销售),sum(东北库存),
sum(总仓销售),sum(总仓库存),sum(全国总计销售),sum(全国总计库存)
from #kd group by 类别 order by 类别
')
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+']'
from 数据表 order by Fid
exec('
select 类别 ,货号,单价'+@s+',
在途库存, 办事处销售,办事处库存,
东北销售,东北库存,总仓销售,总仓库存,全国总计销售,全国总计库存
into #kd
from ##ryk
')
--2.生成汇总处理的
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+']=sum(['+Fname+'])'
from 数据表 order by Fid
exec('
select 类别+''小计'','''',avg(单价*0)'+@s+',
sum(在途库存),sum(办事处销售),sum(办事处库存),sum(东北销售),sum(东北库存),
sum(总仓销售),sum(总仓库存),sum(全国总计销售),sum(全国总计库存)
from #kd group by 类别 order by 类别
')
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+']=sum(['+Fname+'])'
from 数据表 order by Fid
exec('
select 类别+''小计'','''',avg(单价*0)'+@s+',
sum(在途库存),sum(办事处销售),sum(办事处库存),sum(东北销售),sum(东北库存),
sum(总仓销售),sum(总仓库存),sum(全国总计销售),sum(全国总计库存)
from #kd group by 类别 order by 类别
')
set **销售=null
where **销售=0
declare @s varchar(8000)
set @s=''
select @s=@s+',['+Fname+'销售]=case ['+Fname+'销售] when 0 then null else ['+Fname+'] end'
from 数据表
set @s=stuff(@s,1,1,'')
exec('
update stkd set '+@s+'
')