insert into test1
select deptname_column,
sum(case month(date_column) when 1 then sub_total_column end) as Jan,
sum(case month(date_column) when 2 then sub_total_column end) as Feb,
sum(case month(date_column) when 3 then sub_total_column end) as Mar,
...
sum(sub_total_column) as total
from BillCount
group by deptname_column
insert into test1
select deptname_column,
sum(case month(date_column) when 1 then sub_total_column end) as Jan,
sum(case month(date_column) when 2 then sub_total_column end) as Feb,
sum(case month(date_column) when 3 then sub_total_column end) as Mar,
...
from BillCount
group by deptname_column
select *
from (
select [部门名称],[合计],month([日期]) as [月份]
from BillCount
)a
pivot(
sum([合计])
for [月份]
in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)P