月销售报表统计

declare @ta table(pro_id int,pro_name varchar(10),sale_time datetime,sale_qty int)
insert @ta
select 1,'A','2008-09-10',10 union all
select 1,'A','2008-09-12',5 union all
select 1,'A','2008-08-12',9 union all
select 2,'B','2008-08-12',8 union all
select 2,'B','2008-10-12',13 union all
select 1,'A','2008-06-12',2
--查询出所有的数据
select * from @ta
declare @year varchar(10)
set @year='2008'
--方案－
select pro_id,pro_name,year(sale_time) as year,
(select isnull(sum(sale_qty),0) as jun  from @ta A
where year(A.sale_time) = @year and month(A.sale_time)='1' and A.pro_id = B.pro_id
)as jun ,
(select isnull(sum(sale_qty),0) as feb  from @ta A
where year(A.sale_time) = @year and month(A.sale_time)='2'and A.pro_id = B.pro_id
)as feb,

(select isnull(sum(sale_qty),0) as mar  from @ta A
where year(A.sale_time) = @year and month(A.sale_time)='3' and A.pro_id = B.pro_id
)as mar ,
(select isnull(sum(sale_qty),0) as apr  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='4' and A.pro_id = B.pro_id
)as apr ,
(select isnull(sum(sale_qty),0) as may  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='5' and A.pro_id = B.pro_id
) as may,
(select isnull(sum(sale_qty),0) as jun  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='6' and A.pro_id = B.pro_id
) as jun,
(select isnull(sum(sale_qty),0) as jul  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='7'and A.pro_id = B.pro_id
) as jul,

(select isnull(sum(sale_qty),0) as aug  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='8' and A.pro_id = B.pro_id
) as aug ,
(select isnull(sum(sale_qty),0) as sep  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='9'and A.pro_id = B.pro_id
) as sep,
(select isnull(sum(sale_qty),0) as oct  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='10'and A.pro_id = B.pro_id
) as oct ,
(select isnull(sum(sale_qty),0) as nov  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='11'and A.pro_id = B.pro_id
) as nov ,
(select isnull(sum(sale_qty),0) as dec  from @ta A
where year(A.sale_time)= @year and month(A.sale_time)='12'  and A.pro_id = B.pro_id
)as dec ,
(select isnull(sum(sale_qty),0)  as total from @ta A
where year(A.sale_time)= @year and  A.pro_id = B.pro_id
) as total
from @ta B
where year(B.sale_time)=@year
group by B.pro_id,B.pro_name,year(B.sale_time)
order by year(B.sale_time)

-- 方案二

  select pro_id ,pro_name,year(sale_time)as year,
        sum(case when month(sale_time)='1' then sale_qty else 0 end) jun,
sum(case when month(sale_time)='2' then sale_qty else 0 end) feb,
sum(case when month(sale_time)='3' then sale_qty else 0 end) mar,
        sum(case when month(sale_time)='4' then sale_qty else 0 end) apr,
        sum(case when month(sale_time)='5' then sale_qty else 0 end) may,
        sum(case when month(sale_time)='6' then sale_qty else 0 end) jun,
        sum(case when month(sale_time)='7' then sale_qty else 0 end) jul,
        sum(case when month(sale_time)='8' then sale_qty else 0 end) aug,
        sum(case when month(sale_time)='9' then sale_qty else 0 end) sep,
        sum(case when month(sale_time)='10' then sale_qty else 0 end) oct,
        sum(case when month(sale_time)='11' then sale_qty else 0 end) nov,
        sum(case when month(sale_time)='12' then sale_qty else 0 end) dec,
        sum(case when year(sale_time)=@year then sale_qty else 0 end) dec
  from @ta
  where year(sale_time)=@year
  group by pro_id ,pro_name,year(sale_time)
  order by year(sale_time)