可以完成,思路如下:
通过pid 商品编码分组,得到销售日期的每个月列,后用sum(case Fact_m WHEN 月份 then 数量end)来操作即可.
--年度售额:
select pid 商品编码
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '01' then cCost end)),0) 'countCost_1'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '02' then cCost end)),0) 'countCost_2'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '03' then cCost end)),0) 'countCost_3'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '04' then cCost end)),0) 'countCost_4'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '05' then cCost end)),0) 'countCost_5'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '06' then cCost end)),0) 'countCost_6'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '07' then cCost end)),0) 'countCost_7'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '08' then cCost end)),0) 'countCost_8'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '09' then cCost end)),0) 'countCost_9'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '10' then cCost end)),0) 'countCost_10'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '11' then cCost end)),0) 'countCost_11'
,isnull(convert(dec(18,2),sum(case Fact_m WHEN '12' then cCost end)),0) 'countCost_12'
,isnull(convert(dec(18,2),sum(cCost))) 'countTotal'
from Tab group by pid
--Fact_m 指的就是销售日期[月],最后还有一列为年度总计
希望能帮到你!