只有兩個倉庫嗎?
select 存货编号,...,sum(b.数量) as 仓库1数量,
sum(c.数量) as 仓库2数量,sum(d.数量) as 总数量
from 表1 a left join 表2 b on a.存货编号=b.存货编号 and b.仓库名=仓库1
left join 表2 c on a.存货编号=c.存货编号 and c.仓库名=仓库2
left join 表2 d on a.存货编号=d.存货编号
select 存货编号,...,sum(b.数量) as 仓库1数量,
sum(c.数量) as 仓库2数量,sum(d.数量) as 总数量
from 表1 a left join 表2 b on a.存货编号=b.存货编号 and b.仓库名=仓库1
left join 表2 c on a.存货编号=c.存货编号 and c.仓库名=仓库2
left join 表2 d on a.存货编号=d.存货编号
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(仓库名) from 表2 where 存货编号=@content
select @str=right(@str,len(@str)-1) where @str<>''
return @str
end
go--调用:
select *,dbo.getstr(存货编号) 存货编号 from 表1
-------------------------------
你还是要这个?
declare @sql varchar(8000)
set @sql = 'select 存货编号'
select @sql = @sql + ',sum(case 季度 when '''+仓库名+''' then 数量 else 0 end) ['+仓库名+'数量]'
from (select distinct 仓库名 from 有一表) as a
select @sql = @sql+' from 有一表 group by 存货编号'exec(@sql)
go
set @sql='select 存货编号'
select @sql=@sql+',sum(case 仓库名 when '''+仓库名+''' then 数量 else 0 end) ['+仓库名+'数量]'
from (select distinct 仓库名 from 表2) a
select @sql=@sql+' from 表2 group by 存货编号'
--print @sql
exec(@sql)
set @s = 'select 表1.存货编号,表1.其它字段'
select @s = @s + ',sum(case when 表2.仓库名 ='''+ 仓库名 + ''' then 表2.数量 else 0 end) as [' +仓库名+']'
from (select distinct 仓库名 from 表2) A order by 仓库名
set @s = @s + ' from 表1 left join 表2 on 表1.存货编号 = 表2.存货编号 group by 表1.存货编号,表1.其它字段'
exec (@s)愉快的登山者 ⊙
◢◣◢◣◢◣
set @sql='select 存货编号'
select @sql=@sql+',sum(case 仓库名 when '''+仓库名+''' then 数量 else 0 end) ['+仓库名+'数量]'
from (select distinct 仓库名 from 表2) a
select @sql=@sql+' from 表2 group by 存货编号'
--print @sql
exec(@sql)
set @sql='select 存货编号'
select @sql=@sql+',sum(case 仓库名 when '''+仓库名+''' then 数量 else 0 end) ['+仓库名+'数量]'
from (select distinct 仓库名 from 表2) a
select @sql=@sql+' from 表2 group by 存货编号'
--print @sql
exec(@sql)
set @s = 'select 表1.存货编号,表1.其它字段'
select @s = @s + ',sum(case when 表2.仓库名 ='''+ 仓库名 + ''' then 表2.数量 else 0 end) as [' +仓库名+']'
from (select distinct 仓库名 from 表2) A order by 仓库名
set @s = @s + ',sum(表2.数量) 总数量'
set @s = @s + ' from 表1 left join 表2 on 表1.存货编号 = 表2.存货编号 group by 表1.存货编号,表1.其它字段'
exec (@s)愉快的登山者 ⊙
◢◣◢◣◢◣
set @sql='select 存货编号'
select @sql=@sql+',sum(case 仓库名 when '''+仓库名+''' then 数量 else 0 end) ['+仓库名+'数量]'
from (select distinct 仓库名 from wwww) a
select @sql=@sql+',sum(数量) as 总数量 from wwww group by 存货编号'
--print @sql
exec(@sql)
create function SumFunction(@ColValue varchar(10))
returns varchar(1000)
as
begin
declare @strSum varchar(1000)
set @strSum=''
select @strSum = convert(varchar(10),数量)+@strSum from 表2
where 存货编号 = @ColValue
return @strSum
end
--step2 Select
select 存货编号,SumFunction(存货编号),仓库数量 from 表1
表1:Stockstockid ...
-----------------
S001 ...
S002 ...
...表2:Stodetstockid Storage Qty
---------------------------
S001 成品仓 100
S001 次品仓 20
S002 成品仓 200
S002 次品仓 5
...执行SQL后:stockid ... 成品仓数量 次品仓数量 总数量
----------------------------------------------------
S001 ... 100 20 120
S002 ... 200 5 205
...
1.查询时并不能提供“仓库名“,只能提供"Stockid".
2.表2中的Storage有可能增加,比如增加一行:
stockid Storage Qty
---------------------------
S001 坏料仓 10
set @sql='select b.stockid'
select @sql=@sql+',sum(case c.Storage when '''+Storage+''' then Qty else 0 end) ['+Storage+'数量]'
from (select distinct Storage from stodet) a
select @sql=@sql+',sum(c.Qty) as 总数量 from Stock b left join Stodet c on b.stockid =c.stockid group by b.stockid '
--print @sql
exec(@sql)