条件:
1.有出入库两表(部门用部门代码:BMDM)
2.入库A物品 100000个
3.有1000个部门领用这100000个物品(领用数不等,有的部门领,有的没有领)如何实现下面格式:名称 入库数量 单价 部门1领用数 部门2领用数 部门3领用数.....(最多1000)
-------------------------------------------------------------------------------
A物品 100000 23.00 12 23 120 .....
B物品 200000 232.00 212 223 2120 .....
1.有出入库两表(部门用部门代码:BMDM)
2.入库A物品 100000个
3.有1000个部门领用这100000个物品(领用数不等,有的部门领,有的没有领)如何实现下面格式:名称 入库数量 单价 部门1领用数 部门2领用数 部门3领用数.....(最多1000)
-------------------------------------------------------------------------------
A物品 100000 23.00 12 23 120 .....
B物品 200000 232.00 212 223 2120 .....
(
ItemNo char(3),
Amount int,
Cost decimal(10,2)
)
create table Stock_Out
(
ItemNo char(3),
BranchNo char(4),
Amount int
)
insert into Stock_In
select '001',1000,10 union all
select '002',2000,15insert into Stock_Out
select '001','0001',12 union all
select '001','0002',14 union all
select '001','0101',11 union all
select '001','0301',20 union all
select '001','0500',12 union all
select '001','1000',19 union all
select '002','0001',32 union all
select '002','0002',24 union all
select '002','0301',41 union all
select '002','0901',10 union all
select '002','0700',2 union all
select '002','1000',19
go
declare @sql1 varchar(8000),
@sql2 varchar(8000),
@sql3 varchar(8000),
@sql4 varchar(8000),
@sql5 varchar(8000),
@sql6 varchar(8000),
@sql7 varchar(8000),
@sql8 varchar(8000),
@sql9 varchar(8000),
@sql10 varchar(8000)
select @sql1='',@sql2='',@sql3='',@sql4='',@sql5='',@sql6='',@sql7='',@sql8='',@sql9='',@sql10=''
select @sql1=@sql1+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0001' and '0100' group by BranchNo
select @sql2=@sql2+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0101' and '0200' group by BranchNo
select @sql3=@sql3+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0201' and '0300' group by BranchNo
select @sql4=@sql4+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0301' and '0400' group by BranchNo
select @sql5=@sql5+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0401' and '0500' group by BranchNo
select @sql6=@sql6+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0501' and '0600' group by BranchNo
select @sql7=@sql7+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0601' and '0700' group by BranchNo
select @sql8=@sql8+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0701' and '0800' group by BranchNo
select @sql9=@sql9+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0801' and '0900' group by BranchNo
select @sql10=@sql10+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out where BranchNo between '0901' and '1000' group by BranchNoexec ('select SI.ItemNo,SI.Amount,SI.Cost'+@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10+' from Stock_In SI inner join Stock_Out SO on SI.ItemNo=SO.ItemNo group by SI.ItemNo,SI.Amount,SI.Cost')go
drop table stock_in,stock_out
作为列去处理,然后导出Excel后,再转换过来!
试用临时表
select identity(int , 1, 1) as id, * into #t
from Stock_In
然后 在使用 夜大哥的sql
记得最好 drop table
;)
在夜游大哥的sql上稍作修改 :)
select top 100 @sql1=@sql1+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out group by BranchNo order by BranchNo asc
select top 100 @sql2=@sql2+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 100 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql3=@sql3+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 200 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql4=@sql4+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 300 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql5=@sql5+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 400 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql6=@sql6+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 500 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql7=@sql7+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 600 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql8=@sql8+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 700 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql9=@sql9+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 800 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
select top 100 @sql10=@sql10+',sum(case when BranchNo='''+BranchNo+''' then SO.Amount else 0 end) as [部门'+BranchNo+']' from Stock_Out
where BranchNo not in (select top 900 BranchNo from Stock_Out group by BranchNo order by BranchNo asc) group by BranchNo order by BranchNo
^^;一个裤头能发120分的帖了??
---------------------------------人家是别的版块里面的'牛人'啊...这里多数人都会混N个板块的....:) 争取在三个板块里面都升星。