select a.编号,a.名称,a.规格,a.价格
sum(case when 仓库=1 then 库存量 else 0 end) 仓库1量,
sum(case when 仓库=2 then 库存量 else 0 end) 仓库2量
from leechdom a
left join pharmacy b
on a.编号=b.编号
group by a.编号,a.名称,a.规格,a.价格
sum(case when 仓库=1 then 库存量 else 0 end) 仓库1量,
sum(case when 仓库=2 then 库存量 else 0 end) 仓库2量
from leechdom a
left join pharmacy b
on a.编号=b.编号
group by a.编号,a.名称,a.规格,a.价格
set @sql='select a.编号,a.名称,a.规格,a.价格'
select @sql=@sql+',sum(case when b.仓库='+ltrim(仓库)+' then b.库存量 else 0 end) [仓库'+ltrim(仓库)+']'
from (select distinct 仓库 from pharmacy) texec (@sql+' from leechdom a left join pharmacy b on a.编号=b.编号 group by a.编号,a.名称,a.规格,a.价格')
if object_id('tempdb.dbo.#pharmacy') is not null drop table #pharmacy
create table #pharmacy (ID int,药品编号 int,库存量 int,仓库 int,有效期 datetime)
insert into #pharmacy
select 1,1,1,1,'2009-11-12' union all
select 2,1,3,1,'2008-12-12' union all
select 3,2,4,2,'2008-12-12'
--> 测试数据: #leechdom
if object_id('tempdb.dbo.#leechdom') is not null drop table #leechdom
create table #leechdom (编号 int,名称 varchar(3),规格 varchar(4),价格 int)
insert into #leechdom
select 1,'abc','abc',14 union all
select 2,'cde','cdaf',12select
药品编号=a.编号,
名称=max(a.名称),
规格=max(a.规格),
价格=max(a.价格),
仓库1量=sum(case b.仓库 when 1 then 库存量 else 0 end),
仓库2量=sum(case b.仓库 when 2 then 库存量 else 0 end)
from #leechdom a
join #pharmacy b on a.编号=b.药品编号
group by a.编号/*
药品编号 名称 规格 价格 仓库1量 仓库2量
----------- ---- ---- ----------- ----------- -----------
1 abc abc 14 4 0
2 cde cdaf 12 0 4
*/
insert into pharmacy select 1 , 1 , 1 , 1 , '2009-11-12'
insert into pharmacy select 2 , 1 , 3 , 1 , '2008-12-12'
insert into pharmacy select 3 , 2 , 4 , 2 , '2008-12-12' create table leechdom(编号 int,名称 varchar(10),规格 varchar(10),价格 int)
insert into leechdom select 1 , 'abc', 'abc' , 14
insert into leechdom select 2 , 'cde', 'cdaf', 12
go
declare @sql varchar(8000)
set @sql='select a.编号,a.名称,a.规格,a.价格'
select @sql=@sql+',sum(case when b.仓库='+ltrim(仓库)+' then b.库存量 else 0 end) [仓库'+ltrim(仓库)+']'
from (select distinct 仓库 from pharmacy) texec (@sql+' from leechdom a left join pharmacy b on a.编号=b.药品编号 group by a.编号,a.名称,a.规格,a.价格')
go
drop table pharmacy,leechdom/*编号 名称 规格 价格 仓库1 仓库2
----------- ---------- ---------- ----------- ----------- -----------
1 abc abc 14 4 0
2 cde cdaf 12 0 4
*/