set @s = 'select isnull(Department.cDepName,''合计'') as 区域'
select @s = @s + ',['+InventoryClass.cInvCName+']=sum(case when InventoryClass.cInvCName='''+InventoryClass.cInvCName+'''
then DispatchLists.iQuantity else 0 end)'
from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where DispatchLists.DLID = DispatchList.DLID and
DispatchList.cDepCode = Department.cDepCode and
DispatchLists.cInvCode = Inventory.cInvCode and
Inventory.cInvCCode = InventoryClass.cInvCCode
and Inventoryclass.cInvccode like '010200%'
group by InventoryClass.cInvCName
set @s=@s+'合计=sum(DispatchLists.iQuantity) from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where Department.cdepname like ''%办事处%'' and Department.cDepCode like ''02%''
and DispatchList.dDate >=''8/1/2005''
and DispatchList.dDate <=''8/9/2005''
and DispatchLists.DLID = DispatchList.DLID
and DispatchList.cDepCode = Department.cDepCode
and DispatchLists.cInvCode = Inventory.cInvCode
and DispatchLists.cInvCode = Inventory.cInvCode
and Inventory.cInvCCode = InventoryClass.cInvCCode
group by Department.cDepName with rollup'
exec(@s)
select @s = @s + ',['+InventoryClass.cInvCName+']=sum(case when InventoryClass.cInvCName='''+InventoryClass.cInvCName+'''
then DispatchLists.iQuantity else 0 end)'
from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where DispatchLists.DLID = DispatchList.DLID and
DispatchList.cDepCode = Department.cDepCode and
DispatchLists.cInvCode = Inventory.cInvCode and
Inventory.cInvCCode = InventoryClass.cInvCCode
and Inventoryclass.cInvccode like '010200%'
group by InventoryClass.cInvCName
set @s=@s+'合计=sum(DispatchLists.iQuantity) from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where Department.cdepname like ''%办事处%'' and Department.cDepCode like ''02%''
and DispatchList.dDate >=''8/1/2005''
and DispatchList.dDate <=''8/9/2005''
and DispatchLists.DLID = DispatchList.DLID
and DispatchList.cDepCode = Department.cDepCode
and DispatchLists.cInvCode = Inventory.cInvCode
and DispatchLists.cInvCode = Inventory.cInvCode
and Inventory.cInvCCode = InventoryClass.cInvCCode
group by Department.cDepName with rollup'
exec(@s)
-----------------------------------------------------------------------
set @s=@s+'from DispatchLists,DispatchList, Department, Inventory, InventoryClass ...==>set @s=@s+',[合计]=sum(DispatchLists.iQuantity) from DispatchLists,DispatchList, Department, Inventory, InventoryClass ...
declare @s varchar(8000)set @s='select isnull(Department.cDepName,''合计'') as 区域'
select @s=@s+',['+InventoryClass.cInvCName+']=sum(case when InventoryClass.cInvCName='''+InventoryClass.cInvCName+'''
then DispatchLists.iQuantity else 0 end)'
from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where DispatchLists.DLID = DispatchList.DLID and
DispatchList.cDepCode = Department.cDepCode and
DispatchLists.cInvCode = Inventory.cInvCode and
Inventory.cInvCCode = InventoryClass.cInvCCode
and Inventoryclass.cInvccode like '010200%'
group by InventoryClass.cInvCName
set @s=@s+' from DispatchLists,DispatchList, Department, Inventory, InventoryClass
where Department.cdepname like ''%办事处%'' and Department.cDepCode like ''02%''
and DispatchList.dDate >=''8/1/2005''
and DispatchList.dDate <=''8/9/2005''
and DispatchLists.DLID = DispatchList.DLID
and DispatchList.cDepCode = Department.cDepCode
and DispatchLists.cInvCode = Inventory.cInvCode
and DispatchLists.cInvCode = Inventory.cInvCode
and Inventory.cInvCCode = InventoryClass.cInvCCode
group by Department.cDepName with rollup'
exec(@s)