----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-26 17:37:39
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
----> 测试数据[商品表]
--if object_id('商品表') is not null drop table 商品表
--go
--create table 商品表([商品ID] int,[商品名称] nvarchar(4))
--insert 商品表
--select 101,N'花生' union all
--select 102,N'啤酒'
----> 测试数据[仓库表]
--if object_id('仓库表') is not null drop table 仓库表
--go
--create table 仓库表([仓库ID] int,[仓库名称] nvarchar(4))
--insert 仓库表
--select 201,N'仓1' union all
--select 202,N'仓2'
----> 测试数据[库存表]
--if object_id('库存表') is not null drop table 库存表
--go
--create table 库存表([商品ID] int,[仓库ID] int,[商品数量] int)
--insert 库存表
--select 101,201,100 union all
--select 101,202,50 union all
--select 102,201,80
--------------生成数据--------------------------
IF OBJECT_ID('TempDB..#t','u')IS NOT NULL
DROP TABLE #t
select c.商品ID ,c.商品名称,b.仓库名称,a.商品数量 INTO #t
from 库存表 a INNER JOIN 仓库表 b ON a.仓库ID=b.仓库ID
INNER JOIN 商品表 c ON a.商品ID=c.商品ID
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(仓库名称)+N'=sum(case when [仓库名称]=N'+quotename(仓库名称,'''')+N' then [商品数量] else 0 end)'
from #t group by 仓库名称
exec(N'select [商品ID],商品名称'+@s+N',[合计]=sum(商品数量) from #t group by [商品ID],商品名称 order by 商品ID')
----------------结果----------------------------
/*
商品ID 商品名称 仓1 仓2 合计
----------- ---- ----------- ----------- -----------
101 花生 100 50 150
102 啤酒 80 0 80
*/
如果添加个没有库存的商品，就显示不出来，如何能添加个没有库存的商品也显示出来
------解决方案--------------------
你测测，我把没有库存的直接丢仓1，反正都是0
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-26 17:37:39
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[商品表]
if object_id('商品表') is not null drop table 商品表
go
create table 商品表([商品ID] int,[商品名称] nvarchar(4))
insert 商品表
select 101,N'花生' union all
select 102,N'啤酒'union all
select 103,N'毛线'
--> 测试数据[仓库表]
if object_id('仓库表') is not null drop table 仓库表
go
create table 仓库表([仓库ID] int,[仓库名称] nvarchar(4))
insert 仓库表
select 201,N'仓1' union all
select 202,N'仓2'
--> 测试数据[库存表]
if object_id('库存表') is not null drop table 库存表
go
create table 库存表([商品ID] int,[仓库ID] int,[商品数量] int)
insert 库存表
select 101,201,100 union all
select 101,202,50 union all
select 102,201,80
--------------生成数据--------------------------
IF OBJECT_ID('TempDB..#t','u')IS NOT NULL
DROP TABLE #t
select c.商品ID ,c.商品名称,ISNULL(b.仓库名称,'仓1')仓库名称,ISNULL(a.商品数量,0)商品数量 INTO #t
from 库存表 a left JOIN 仓库表 b ON a.仓库ID=b.仓库ID
right JOIN 商品表 c ON a.商品ID=c.商品ID
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(仓库名称)+N'=sum(case when [仓库名称]=N'+quotename(仓库名称,'''')+N' then [商品数量] else 0 end)'
from #t group by 仓库名称
exec(N'select [商品ID],商品名称'+@s+N',[合计]=isnull(sum(商品数量),0) from #t group by [商品ID],商品名称 order by 商品ID')