1月用量 统计:
供应商 code quantity
K017 ink01 15
2月用量 统计:
供应商 code quantity
K017 ink01 20
3月用量 统计:
供应商 code quantity
K017 ink01 110
K019 ink01 20 要得到下面效果:
供应商 code 1月 2月 3月 4月,......
K017 ink01 15 20 110
K019 ink01 null null 20应该怎么做?
供应商 code quantity
K017 ink01 15
2月用量 统计:
供应商 code quantity
K017 ink01 20
3月用量 统计:
供应商 code quantity
K017 ink01 110
K019 ink01 20 要得到下面效果:
供应商 code 1月 2月 3月 4月,......
K017 ink01 15 20 110
K019 ink01 null null 20应该怎么做?
b.quantity as [1月],
c.quantity as [2月],
d.quantity as [3月],
e.quantity as [4月],
f.quantity as [5月],
g.quantity as [6月],
h.quantity as [7月],
i.quantity as [8月],
j.quantity as [9月],
k.quantity as [10月],
l.quantity as [11月],
m.quantity as [12月]
from (
select 供应商,code from [1月供应量]
union
select 供应商,code from [2月供应量]
union
select 供应商,code from [3月供应量]
union
select 供应商,code from [4月供应量]
union
select 供应商,code from [5月供应量]
union
select 供应商,code from [6月供应量]
union
select 供应商,code from [7月供应量]
union
select 供应商,code from [8月供应量]
union
select 供应商,code from [9月供应量]
union
select 供应商,code from [10月供应量]
union
select 供应商,code from [11月供应量]
union
select 供应商,code from [12月供应量]
)a left join [1月供应量] b on a.供应商=b.供应商 and a.code=b.code
left join [2月供应量] c on a.供应商=c.供应商 and a.code=c.code
left join [3月供应量] d on a.供应商=d.供应商 and a.code=d.code
left join [4月供应量] e on a.供应商=e.供应商 and a.code=e.code
left join [5月供应量] f on a.供应商=f.供应商 and a.code=f.code
left join [6月供应量] g on a.供应商=g.供应商 and a.code=g.code
left join [7月供应量] h on a.供应商=h.供应商 and a.code=h.code
left join [8月供应量] i on a.供应商=i.供应商 and a.code=i.code
left join [9月供应量] j on a.供应商=j.供应商 and a.code=j.code
left join [10月供应量] k on a.供应商=k.供应商 and a.code=k.code
left join [11月供应量] l on a.供应商=l.供应商 and a.code=l.code
left join [12月供应量] m on a.供应商=m.供应商 and a.code=m.code
isnull(b.[sum],0) [2月]
from
(select 供应商,code,sum(quantity) [sum] from tb1) a,
(select 供应商,code,sum(quantity) [sum] from tb1) b
full join on a.供应商=b.供应商 and a.code=b.code
sum(case when 月份=1 then quantity else 0 end)[1月],
sum(case when 月份=2 then quantity else 0 end)[2月],
sum(case when 月份=3 then quantity else 0 end)[3月],
sum(case when 月份=4 then quantity else 0 end)[4月],
sum(case when 月份=5 then quantity else 0 end)[5月],
sum(case when 月份=6 then quantity else 0 end)[6月],
sum(case when 月份=7 then quantity else 0 end)[7月],
sum(case when 月份=8 then quantity else 0 end)[8月],
sum(case when 月份=9 then quantity else 0 end)[9月],
sum(case when 月份=10 then quantity else 0 end)[10月],
sum(case when 月份=11 then quantity else 0 end)[11月],
sum(case when 月份=12 then quantity else 0 end)[12月]
from tb group by 供应商,code