declare @intCount int,@DepotCount int,@SqlStr varchar(800)select @DepotCount=(select count(whbh) from ytwarehouse)
select @intCount=1
select @SqlStr=''
while @intCount<=@DepotCount
begin
select @SqlStr=@SqlStr+',isnull(sum(case t1.whbh when '+@intCount +' then whsl else 0 end),0) as ' + (select whname from ytwarehouse where whbh=cast(@intCount as varchar)) select @intCount=@intCount+1
end
select @SqlStr='select t2.hbbm,t2.hbname'+@SqlStr + 'from (select hbbm,whbh,数量=sum(whsl) from ytfwh group by hbbm,whbh) t1 right join ythbzlb t2 on t1.hbbm=t2.hbbm'exec(@SqlStr)这是全部的语句
select @intCount=1
select @SqlStr=''
while @intCount<=@DepotCount
begin
select @SqlStr=@SqlStr+',isnull(sum(case t1.whbh when '+@intCount +' then whsl else 0 end),0) as ' + (select whname from ytwarehouse where whbh=cast(@intCount as varchar)) select @intCount=@intCount+1
end
select @SqlStr='select t2.hbbm,t2.hbname'+@SqlStr + 'from (select hbbm,whbh,数量=sum(whsl) from ytfwh group by hbbm,whbh) t1 right join ythbzlb t2 on t1.hbbm=t2.hbbm'exec(@SqlStr)这是全部的语句
select @intCount=1
select @SqlStr=''
while @intCount<=@DepotCount
begin
select @SqlStr=@SqlStr+',isnull(sum(case t1.whbh when '+cast(@intCount as varchar(10))+' then whsl else 0 end),0) as ' + (select whname from ytwarehouse where whbh=cast(@intCount as varchar)) select @intCount=@intCount+1
end
select @intCount=1
select @SqlStr=''
while @intCount<=@DepotCount
begin
select @SqlStr=@SqlStr+',isnull(sum(case t1.whbh when '+cast(@intCount as varchar(10))+' then whsl else 0 end),0) as ' + (select whname from ytwarehouse where whbh=cast(@intCount as varchar)) select @intCount=@intCount+1
endselect @SqlStr='select t2.hbbm,t2.hbname '+@SqlStr + ' from (select hbbm,whbh,数量=sum(whsl) from ytfwh group by hbbm,whbh) t1 right join ythbzlb t2 on t1.hbbm=t2.hbbm'
exec(@SqlStr)服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 't1' 附近有语法错误。
from (select hbbm,whbh,数量=sum(whsl) from ytfwh group by hbbm,whbh) T1 right join ythbzlb t2 on t1.hbbm=t2.hbbm服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ' ' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'T1' 附近有语法错误。
insert #ythbzlb values('001','酒')
insert #ythbzlb values('002','烟')create table #ytwarehouse(whbh int,whname varchar(100))
insert #ytwarehouse values(1, '一号仓库')
insert #ytwarehouse values(2 , '二号仓库')create table #ytfwh(whbh int,hbbm varchar(10),sl int)
insert #ytfwh values(1,'001', 3)
insert #ytfwh values(1,'002', 5)
insert #ytfwh values(2,'001', 6)
insert #ytfwh values(2,'001' , 1)select a.hbbm,a.hbname,c.whname,b.sl from #ythbzlb a join #ytfwh b on a.hbbm=b.hbbm join #ytwarehouse c on b.whbh=c.whbhdeclare @sql varchar(8000),@col varchar(4000)
select @sql = 'select a.hbbm,a.hbname',@col=''
select @sql = @sql + ',sum(case c.whname when '''+whname+''' then sl else 0 end) as '+whname,@col=@col+whname+'+'
from (select top 100000 whname from #ytwarehouse group by whname order by whname desc) as a
select @sql = @sql+' into #临时表 from #ythbzlb a join #ytfwh b on a.hbbm=b.hbbm join #ytwarehouse c on b.whbh=c.whbh group by a.hbbm,a.hbname ; select *,'+left(@col,len(@col)-1)+' 合计 from #临时表'exec(@sql)
go
drop table #ythbzlb
drop table #ytwarehouse
drop table #ytfwh