表结构
日期 编号 位置 数量
2009-11-19 B001 A 10
2009-11-19 B002 A 5
2009-11-19 B003 A 10
2009-11-19 B004 B 10
2009-11-19 B005 B 10
2009-11-19 B006 C 10
2009-11-19 B007 C 10
2009-11-19 B008 D 10
2009-11-19 B009 D 10
2009-11-19 B001 B 10
2009-11-19 B002 C 10
2009-11-19 B003 D 10
2009-11-19 B004 A 10
查询结果
(A的数量) (B的数量) (C的数量) (D的数量)
日期 编号 数量 数量 数量 数量
2009-11-19 B001 10 10 0 0
2009-11-19 B002 5 0 10 0
2009-11-19 B003 10 0 0 10
2009-11-19 B004 10 10 0 0
2009-11-19 B005 0 10 0 0
2009-11-19 B006 0 0 10 0
2009-11-19 B007 0 0 10 0
2009-11-19 B008 0 0 0 10
2009-11-19 B009 0 0 0 10请贴出查询语句,谢谢
日期 编号 位置 数量
2009-11-19 B001 A 10
2009-11-19 B002 A 5
2009-11-19 B003 A 10
2009-11-19 B004 B 10
2009-11-19 B005 B 10
2009-11-19 B006 C 10
2009-11-19 B007 C 10
2009-11-19 B008 D 10
2009-11-19 B009 D 10
2009-11-19 B001 B 10
2009-11-19 B002 C 10
2009-11-19 B003 D 10
2009-11-19 B004 A 10
查询结果
(A的数量) (B的数量) (C的数量) (D的数量)
日期 编号 数量 数量 数量 数量
2009-11-19 B001 10 10 0 0
2009-11-19 B002 5 0 10 0
2009-11-19 B003 10 0 0 10
2009-11-19 B004 10 10 0 0
2009-11-19 B005 0 10 0 0
2009-11-19 B006 0 0 10 0
2009-11-19 B007 0 0 10 0
2009-11-19 B008 0 0 0 10
2009-11-19 B009 0 0 0 10请贴出查询语句,谢谢
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (日期 datetime,编号 varchar(4),位置 varchar(1),数量 int)
insert into #tb
select '2009-11-19','B001','A',10 union all
select '2009-11-19','B002','A',5 union all
select '2009-11-19','B003','A',10 union all
select '2009-11-19','B004','B',10 union all
select '2009-11-19','B005','B',10 union all
select '2009-11-19','B006','C',10 union all
select '2009-11-19','B007','C',10 union all
select '2009-11-19','B008','D',10 union all
select '2009-11-19','B009','D',10 union all
select '2009-11-19','B001','B',10 union all
select '2009-11-19','B002','C',10 union all
select '2009-11-19','B003','D',10 union all
select '2009-11-19','B004','A',10select 日期,编号,
[数量A]=sum(case when 位置='A' then 数量 else 0 end),
[数量B]=sum(case when 位置='B' then 数量 else 0 end),
[数量C]=sum(case when 位置='C' then 数量 else 0 end),
[数量D]=sum(case when 位置='D' then 数量 else 0 end)
from #tb
group by 日期,编号
日期 编号 数量A 数量B 数量C 数量D
----------------------- ---- ----------- ----------- ----------- -----------
2009-11-19 00:00:00.000 B001 10 10 0 0
2009-11-19 00:00:00.000 B002 5 0 10 0
2009-11-19 00:00:00.000 B003 10 0 0 10
2009-11-19 00:00:00.000 B004 10 10 0 0
2009-11-19 00:00:00.000 B005 0 10 0 0
2009-11-19 00:00:00.000 B006 0 0 10 0
2009-11-19 00:00:00.000 B007 0 0 10 0
2009-11-19 00:00:00.000 B008 0 0 0 10
2009-11-19 00:00:00.000 B009 0 0 0 10(9 行受影响)
insert into @t
select '2009-11-19','B001','a',10 union all
select '2009-11-19','B002','a',5 union all
select '2009-11-19','B003','a',10 union all
select '2009-11-19','B004','b',10 union all
select '2009-11-19','B005','b',10 union all
select '2009-11-19','B006','c',10 union all
select '2009-11-19','B007','c',10 union all
select '2009-11-19','B008','d',10 union all
select '2009-11-19','B009','d',10 union all
select '2009-11-19','B001','b',10 union all
select '2009-11-19','B002','c',10 union all
select '2009-11-19','B003','d',10 union all
select '2009-11-19','B004','a',10
select t,c,
sum(case when p = 'a' then n else 0 end) a,
sum(case when p = 'b' then n else 0 end) b,
sum(case when p = 'c' then n else 0 end) c,
sum(case when p = 'd' then n else 0 end) d
from @t group by t,c(13 行受影响)
t c a b c d
----------------------- -------- ---------------------- ---------------------- ---------------------- ----------------------
2009-11-19 00:00:00.000 B001 10 10 0 0
2009-11-19 00:00:00.000 B002 5 0 10 0
2009-11-19 00:00:00.000 B003 10 0 0 10
2009-11-19 00:00:00.000 B004 10 10 0 0
2009-11-19 00:00:00.000 B005 0 10 0 0
2009-11-19 00:00:00.000 B006 0 0 10 0
2009-11-19 00:00:00.000 B007 0 0 10 0
2009-11-19 00:00:00.000 B008 0 0 0 10
2009-11-19 00:00:00.000 B009 0 0 0 10(9 行受影响)
if object_id('tb') is not null
drop table tb
go
create table tb(日期 date,编号 varchar(5),位置 varchar(2),数量 int)
insert into tb
select '2009-11-19', 'B001', 'A', 10 union all
select '2009-11-19', 'B002', 'A', 5 union all
select '2009-11-19', 'B003', 'A', 10 union all
select '2009-11-19', 'B004', 'B', 10 union all
select '2009-11-19', 'B005', 'B', 10 union all
select '2009-11-19', 'B006', 'C', 10 union all
select '2009-11-19', 'B007', 'C', 10 union all
select '2009-11-19', 'B008', 'D', 10 union all
select '2009-11-19', 'B009', 'D', 10 union all
select '2009-11-19', 'B001', 'B', 10 union all
select '2009-11-19', 'B002', 'C', 10 union all
select '2009-11-19', 'B003', 'D', 10 union all
select '2009-11-19', 'B004', 'A', 10declare @s varchar(8000)
set @s=''
select @s=@s+',' +位置+'的数量 = sum(case 位置 when '''+位置+''' then 数量 else 0 end)'
from tb
group by 位置
exec('select 日期,编号 ' +@s+' from tb group by 日期,编号') 日期 编号 A的数量 B的数量 C的数量 D的数量
2009-11-19 B001 10 10 0 0
2009-11-19 B002 5 0 10 0
2009-11-19 B003 10 0 0 10
2009-11-19 B004 10 10 0 0
2009-11-19 B005 0 10 0 0
2009-11-19 B006 0 0 10 0
2009-11-19 B007 0 0 10 0
2009-11-19 B008 0 0 0 10
2009-11-19 B009 0 0 0 10