110,561
社区成员
发帖
与我相关
我的任务
分享
create table #t
([产品] nvarchar(1),[销售时间] Datetime,[单价] int,[数量] int)
Insert #t
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',12,85 union all
select N'a','2010-01-05',12,85 union all
select N'a','2010-02-01',10,82 union all
select N'a','2010-02-02',10,83 union all
select N'a','2010-03-05',12,80 union all
select N'b','2010-01-01',20,90 union all
select N'b','2010-01-01',22,95 union all
select N'b','2010-02-01',20,92 union all
select N'b','2010-02-02',20,93 union all
select N'b','2010-03-05',21,90 union all
select N'c','2010-01-01',30,70 union all
select N'c','2010-01-01',32,75 union all
select N'c','2010-02-01',30,72 union all
select N'c','2010-02-02',30,73 union all
select N'c','2010-03-02',30,73 union all
select N'c','2010-04-02',30,73 union all
select N'c','2010-05-02',30,73 union all
select N'c','2010-06-02',30,73 union all
select N'c','2010-07-02',30,73 union all
select N'c','2010-08-02',30,73 union all
select N'c','2010-09-02',30,73 union all
select N'c','2010-10-02',30,73 union all
select N'b','2010-07-02',20,93 union all
select N'b','2010-09-05',21,90 union all
select N'c','2010-11-01',30,70 union all
select N'c','2010-11-02',30,73 union all
select N'c','2010-12-05',31,70
select sal_year as sal_year,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
from
(
select [数量]*[单价] as sal,datepart(month,[销售时间]) as sal_month,datepart(year,[销售时间]) as sal_year from #t
)T
pivot
(
sum(sal) for sal_month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)P
drop table #t