有数据表如下
APPNUM appfreight type
WXD-201101-1 200 A
WXD-201101-1 300 A
WXD-201101-1 100 B
WXD-201101-1 200 B
SH103-201101-1 2000 A
SH103-201101-1 1000 B
SH103-201101-1 3000 B
SH103-201101-1 1500 C现要写个视图得到如下数据,根据APPNUM和type求和计算,我用的是sql2000,求高手帮忙,如何写?
APPNUM freightA freightB freightC
WXD-201101-1 500 300 0
SH103-201101-1 2000 4000 1500
APPNUM appfreight type
WXD-201101-1 200 A
WXD-201101-1 300 A
WXD-201101-1 100 B
WXD-201101-1 200 B
SH103-201101-1 2000 A
SH103-201101-1 1000 B
SH103-201101-1 3000 B
SH103-201101-1 1500 C现要写个视图得到如下数据,根据APPNUM和type求和计算,我用的是sql2000,求高手帮忙,如何写?
APPNUM freightA freightB freightC
WXD-201101-1 500 300 0
SH103-201101-1 2000 4000 1500
select APPNUM,sum(case when type='A' then appfreight else 0 end) as freightA,
sum(case when type='B' then appfreight else 0 end) as freightB,
sum(case when type='B' then appfreight else 0 end) as freightB
from tb group by APPNUM
freightA=sum(case when type='A' then appfreight else 0 end),
freightB=sum(case when type='A' then appfreight else 0 end),
freightC=sum(case when type='A' then appfreight else 0 end)
from tb
group by APPNUM
freightA=sum(case when type='A' then appfreight else 0 end),
freightB=sum(case when type='B' then appfreight else 0 end),
freightC=sum(case when type='C' then appfreight else 0 end)
from tb
group by APPNUM
select appnum,
sum(case type when 'A' then appfreight end) freightA,
sum(case type when 'B' then appfreight end) freightB,
sum(case type when 'C' then appfreight end) freightC
from tb
group by appnum;-- 如果 type 值不固定
declare @sql varchar(4000);
set @sql='select appnum';
select @sql=@sql+char(10)+
',sum(case type when '''+type+''' then appfreight end) freight'+type+char(10)+
from tb group by type;
set @sql=@sql+' from tb group by appnum';
-- print @sql;
exec(@sql);
(APPNUM varchar(20), appfreight int, type varchar(10))
insert into #tb select 'WXD-201101-1',200,'A'
union all select 'WXD-201101-1',300,'A'
union all select 'WXD-201101-1',100,'B'
union all select 'WXD-201101-1',200,'B'
union all select 'SH103-201101-1',2000,'A'
union all select 'SH103-201101-1',1000,'B'
union all select 'SH103-201101-1',3000,'B'
union all select 'SH103-201101-1',1500,'C'
declare @var varchar(5000)
select @var= stuff((select ',['+type+']' from #tb
group by type for xml path('')),1,1,'')exec('select APPNUM,'+@var+' from #tb a
pivot(sum(appfreight) for type in('+@var+'))c')APPNUM A B C
-------------------- ----------- ----------- -----------
SH103-201101-1 2000 4000 1500
WXD-201101-1 500 300 NULL(2 行受影响)
create table #tb
(APPNUM varchar(20), appfreight int, type varchar(10))
insert into #tb select 'WXD-201101-1',200,'A'
union all select 'WXD-201101-1',300,'A'
union all select 'WXD-201101-1',100,'B'
union all select 'WXD-201101-1',200,'B'
union all select 'SH103-201101-1',2000,'A'
union all select 'SH103-201101-1',1000,'B'
union all select 'SH103-201101-1',3000,'B'
union all select 'SH103-201101-1',1500,'C'
declare @var varchar(5000)
select @var= stuff((select ',['+type+']' from #tb
group by type for xml path('')),1,1,'')exec('select APPNUM,'+@var+' from #tb a
pivot(sum(appfreight) for type in('+@var+'))c')APPNUM A B C
-------------------- ----------- ----------- -----------
SH103-201101-1 2000 4000 1500
WXD-201101-1 500 300 NULL(2 行受影响)