MSSQL2000 动态仓库库藏格式转换

MSSQL2000 动态仓库库存格式转换
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)