MSSQL2000 动态仓库库存
store仓库表名 store表会动态增加仓库
列
storeid,storename
1 , 成品仓
4 , 配件仓
8 , 原材料仓
物料表material
物料ID， 物料编码，物料名称，物料规格
materialid, code, name,specs
10001 , A , A1 , A2
30005 , K , K1 , K2
30008 , J , J1 , J2
仓库库存视图 view_storeqtysum
仓库ID，物料ID， 数量
storeid,materialid,qty
1 , 10001 , 3000
4 , 10001 , 400
4 , 30005 , 900
8 , 30008 , 30000
求根据store表,如果再增加了仓库后，格式如下,store表会动态增加的。
物料ID，编码，名称，规格， 仓库1ID，成品仓库存, 配件仓库存，原材料仓库存 ......
materialid,code,name,specs,storeid1,storename1,storeid4,storename4,storeid8,storename8 ......
10001 , a, a1 , a2 , 1 , 3000 , 4 , 400 , 8 , 0
30005 , k, k1 , k2 , 1 , 0 , 4 , 900 , 8 , 0
30008 , j, j1 , j2 , 1 , 0 , 4 , 0 , 8 , 30000
------解决方案--------------------
- SQL code
if object_id('material') is not null drop table materialgocreate table material( materialid int, code varchar(1), name varchar(2), specs varchar(2))goinsert into materialselect 10001 , 'A' , 'A1' , 'A2' union allselect 30005 , 'K' , 'K1' , 'K2' union allselect 30008 , 'J' , 'J1' , 'J2'goif object_id('view_storeqtysum') is not null drop table view_storeqtysumgocreate table view_storeqtysum( storeid int, materialid int, qty int )goinsert into view_storeqtysumselect 1 , 10001 , 3000 union allselect 4 , 10001 , 400 union allselect 4 , 30005 , 900 union allselect 8 , 30008 , 30000godeclare @sql varchar(4000)set @sql='select a.materialid,a.code,a.name,a.specs'select @sql=@sql+','+cast(storeid as varchar(2))+' as storeid'+cast(storeid as varchar(2)) +', sum(case when b.storeid='+cast(storeid as varchar(2))+' then qty else 0 end) as storename'+cast(storeid as varchar(2))from (select distinct storeid from view_storeqtysum) tset @sql=@sql+' from material a inner join view_storeqtysum b on a.materialid=b.materialid group by a.materialid,a.code,a.name,a.specs'--print @sqlexec(@sql)/*materialid code name specs storeid1 storename1 storeid4 storename4 storeid8 storename8----------- ---- ---- ----- ----------- ----------- ----------- ----------- ----------- -----------10001 A A1 A2 1 3000 4 400 8 030005 K K1 K2 1 0 4 900 8 030008 J J1 J2 1 0 4 0 8 30000*/
------解决方案--------------------
- SQL code
declare @sql varchar(4000)set @sql='select a.materialid,a.code,a.name,a.specs'select @sql=@sql+','+cast(storeid as varchar(2))+' as storeid'+ cast(storeid as varchar(2))+', sum(case when b.storeid='+cast(storeid as varchar(2))+' then qty else 0 end) as storename'+cast(storeid as varchar(2))from (select distinct storeid from view_storeqtysum) tset @sql=@sql+' from material a inner join view_storeqtysum b on a.materialid=b.materialid group by a.materialid,a.code,a.name,a.specs'exec(@sql)