下边的 20,30是QC仓库,16,13是非限制仓库.
料号,仓库,数量
A111 16 200
A111 16 200
A111 20 200
A222 13 100
A222 13 100
A222 13 100
A222 30 100-->这个请大家帮帮忙,谢谢.
料号 仓库16,仓库20,仓库13,仓库30
A111 400 200
A222 300 100-->这步我想 分别查询出 QC和unrestricted仓库,在用union all就可以了.
料号 QC仓库,unrestricted仓库,
A111 200 400
A222 100 300
料号,仓库,数量
A111 16 200
A111 16 200
A111 20 200
A222 13 100
A222 13 100
A222 13 100
A222 30 100-->这个请大家帮帮忙,谢谢.
料号 仓库16,仓库20,仓库13,仓库30
A111 400 200
A222 300 100-->这步我想 分别查询出 QC和unrestricted仓库,在用union all就可以了.
料号 QC仓库,unrestricted仓库,
A111 200 400
A222 100 300
http://blog.csdn.net/claro/archive/2008/12/03/3435583.aspx相似度99.9%
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
if object_id('[tb_1]') is not null drop table [tb_1]
go
create table [tb_1] (料号 varchar(4),仓库 int,数量 int)
insert into [tb_1]
select 'A111',16,200 union all
select 'A111',16,200 union all
select 'A111',20,200 union all
select 'A222',13,100 union all
select 'A222',13,100 union all
select 'A222',13,100 union all
select 'A222',30,100
-------------------------------------------
declare @s varchar(4000)select @s=isnull(@s+',','')+'sum(case when 仓库= '+ltrim(仓库)+' then 数量 else 0 end) [仓库'+ltrim(仓库)+']'
from (select distinct 仓库 from tb_1)aset @s='select 料号,'+@s +' from tb_1 group by 料号'
exec(@s)
料号 仓库13 仓库16 仓库20 仓库30
---- ----------- ----------- ----------- -----------
A111 0 400 200 0
A222 300 0 0 100(2 行受影响)
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([料号] nvarchar(4),[仓库] int,[数量] int)
Insert T
select N'A111',16,200 union all
select N'A111',16,200 union all
select N'A111',20,200 union all
select N'A222',13,100 union all
select N'A222',13,100 union all
select N'A222',13,100 union all
select N'A222',30,100
Go
DECLARE @s NVARCHAR(4000)
SET @s=N'select [料号]'
Select @s=@s+N',[仓库'+RTRIM([仓库])+N']=sum(case when [仓库]='+RTRIM([仓库])+N' then [数量] else 0 end)' from T GROUP BY [仓库]
EXEC(@s+N' from T group by [料号]')/*
料号 仓库13 仓库16 仓库20 仓库30
A111 0 400 200 0
A222 300 0 0 100
*/
(case SC33002 when '20' then sum(SC33005) end) as "QC WH",
(case SC33002 when '16' then sum(SC33005) end) as "UR WH"
from SC330200 where SC33001='A123101BC'and SC33005<>'0' group by SC33001,SC33002这样写的结果
没办法 放在一行上,还是分开的..
stockcode QC WH UR WH
A123101BC NULL 2562.00000000
A123101BC 829.20000000 NULL-->怎么改改,谢谢
if object_id('[tb_1]') is not null drop table [tb_1]
go
create table [tb_1] (料号 varchar(4),仓库 int,数量 int)
insert into [tb_1]
select 'A111',16,200 union all
select 'A111',16,200 union all
select 'A111',20,200 union all
select 'A222',13,100 union all
select 'A222',13,100 union all
select 'A222',13,100 union all
select 'A222',30,100
--1-----------------------------------------
declare @s varchar(4000)select @s=isnull(@s+',','')+'sum(case when 仓库= '+ltrim(仓库)+' then 数量 else 0 end) [仓库'+ltrim(仓库)+']'
from (select distinct 仓库 from tb_1)aset @s='select 料号,'+@s +' from tb_1 group by 料号'
exec(@s)--2---------------------------------- select 料号,
[QC仓库]=sum(case when 仓库 in (20,30) then 数量 else 0 end),
[unrestricted仓库]=sum(case when 仓库 in (13,16) then 数量 else 0 end)
from tb_1
group by 料号
料号 仓库13 仓库16 仓库20 仓库30
---- ----------- ----------- ----------- -----------
A111 0 400 200 0
A222 300 0 0 100(2 行受影响)料号 QC仓库 unrestricted仓库
---- ----------- --------------
A111 200 400
A222 100 300(2 行受影响)