--> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb ( [ID] int, [部门ID] int, [计划] int, [实际] int, [完成时间] datetime ) insert #tb select 1,1,20,18,'2014-1-2' union all select 2,1,24,16,'2014-1-5' union all select 3,1,25,20,'2014-2-1' union all select 4,1,20,16,'2014-2-4' union all select 5,2,24,20,'2014-1-4' union all select 6,2,20,15,'2014-1-6' union all select 7,2,25,20,'2014-2-6' union all select 8,2,28,25,'2014-2-8' union all select 9,3,22,20,'2014-2-3' union all select 10,3,20,16,'2014-2-6' union all select 11,3,22,19,'2014-3-7' union all select 12,3,25,20,'2014-3-9' goselect [部门ID], SUM(case when MONTH([完成时间])=1 then [计划] else 0 end) as '1月份计划', SUM(case when MONTH([完成时间])=1 then [实际] else 0 end) as '1月份实际', SUM(case when MONTH([完成时间])=2 then [计划] else 0 end) as '2月份计划', SUM(case when MONTH([完成时间])=2 then [实际] else 0 end) as '2月份实际', SUM(case when MONTH([完成时间])=3 then [计划] else 0 end) as '3月份计划', SUM(case when MONTH([完成时间])=3 then [实际] else 0 end) as '3月份实际', SUM(case when MONTH([完成时间])=4 then [计划] else 0 end) as '4月份计划', SUM(case when MONTH([完成时间])=4 then [实际] else 0 end) as '4月份实际' from #tb group by [部门ID] /* 部门ID 1月份计划 1月份实际 2月份计划 2月份实际 3月份计划 3月份实际 4月份计划 4月份实际 ---------------------------------------------------------------------------------------------------------------- 1 44 34 45 36 0 0 0 0 2 44 35 53 45 0 0 0 0 3 0 0 42 36 47 39 0 0 */
create table #temb ( ID int, DepNO int, planSum int, TrueSum int, complete dateTime ) insert into #temb values(1,1,20,18,'2014-1-2') insert into #temb values(2,1,24,16,'2014-1-5') insert into #temb values(3,1,25,20,'2014-2-1') insert into #temb values(4,1,20,16,'2014-2-4') insert into #temb values(5,2,24,20,'2014-1-4') insert into #temb values(6,2,20,15,'2014-1-6') insert into #temb values(7,2,25,20,'2014-2-6') insert into #temb values(8,2,28,25,'2014-2-8') insert into #temb values(9,3,22,20,'2014-2-3') insert into #temb values(10,3,20,16,'2014-2-6') insert into #temb values(11,3,22,19,'2014-3-7') insert into #temb values(12,3,25,20,'2014-3-9') select a.DepNo '部门ID',sum(a.a1)'1月份计划',sum(a.a2)'1月份实际',sum(a.b1)'2月份计划',sum(a.b2)'2月份实际',sum(a.c1)'3月份计划',sum(a.c2)'3月份实际' from (select DepNO,case when month(complete)=1 then sum(planSum) else 0 end as a1, case when month(complete)=1 then sum(TrueSum) else 0 end as a2, case when month(complete)=2 then sum(planSum) else 0 end as b1, Case when month(complete)=2 then sum(TrueSum) else 0 end as b2, case when month(complete)=3 then sum(planSum) else 0 end as c1, case when month(complete)=3 then sum(TrueSum) else 0 end as c2 from #temb group by DepNO,month(complete))a group by a.DepNo
create table test(id int,部门id int, 计划 int,实际 int,时间 datetime) insert test select 1,1,20,18,'2014-1-2' union all select 2,1,24,16,'2014-1-5' union all select 3,1,25,20,'2014-2-1' union all select 4,1,20,16,'2014-2-4' union all select 5,2,24,20,'2014-1-4' union all select 6,2,20,15,'2014-1-6' union all select 7,2,25,20,'2014-2-6' union all select 8,2,28,25,'2014-2-8' union all select 9,3,22,20,'2014-2-3' union all select 10,3,20,16,'2014-2-6' union all select 11,3,22,19,'2014-3-7' union all select 12,3,25,20,'2014-3-9'declare @sql varchar(5000) set @sql='select 部门id' select @sql=@sql+',max( case 时间 when '''+时间+''' then 计划 else 0 end)['+时间+'计划'+']' +',max( case 时间 when '''+时间+''' then 实际 else 0 end)['+时间+'实际'+']' from(select distinct 时间 from ( SELECT b.部门id, b.计划, b.实际, 时间=CONVERT(VARCHAR(7),DATEADD(mm,A.number,'2014-01-01'), 120) from ( select number from master..spt_values where type = 'P' AND number BETWEEN 0 AND 11 ) a left join ( select 部门id, 计划=SUM(计划), 实际=SUM(实际), 日期=CONVERT(varchar(7),时间,120) from test group by 部门id,CONVERT(varchar(7),时间,120) ) b on CONVERT(VARCHAR(7),DATEADD(mm,a.number,'2014-01-01'), 120)=b.日期 ) m ) x set @sql=@sql+'from (SELECT b.部门id, b.计划, b.实际, 时间=CONVERT(VARCHAR(7),DATEADD(mm,A.number,'''+'2014-01-01'+'''), 120) from (select number from master..spt_values where type = '''+'P'+''' AND number BETWEEN 0 AND 11) a left join (select 部门id, 计划=SUM(计划), 实际=SUM(实际), 日期=CONVERT(varchar(7),时间,120) from test group by 部门id,CONVERT(varchar(7),时间,120) ) b on CONVERT(VARCHAR(7),DATEADD(mm,a.number,'''+'2014-01-01'+'''), 120)=b.日期 ) u where 部门id is not null group by 部门id' exec(@sql)/* 1 44 34 45 36 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 44 35 53 45 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0 0 42 36 47 39 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 */
if object_id('tempdb.dbo.#tb') is not null
drop table #tb
go
create table #tb
(
[ID] int,
[部门ID] int,
[计划] int,
[实际] int,
[完成时间] datetime
)
insert #tb
select 1,1,20,18,'2014-1-2' union all
select 2,1,24,16,'2014-1-5' union all
select 3,1,25,20,'2014-2-1' union all
select 4,1,20,16,'2014-2-4' union all
select 5,2,24,20,'2014-1-4' union all
select 6,2,20,15,'2014-1-6' union all
select 7,2,25,20,'2014-2-6' union all
select 8,2,28,25,'2014-2-8' union all
select 9,3,22,20,'2014-2-3' union all
select 10,3,20,16,'2014-2-6' union all
select 11,3,22,19,'2014-3-7' union all
select 12,3,25,20,'2014-3-9'
goselect
[部门ID],
SUM(case when MONTH([完成时间])=1 then [计划] else 0 end) as '1月份计划',
SUM(case when MONTH([完成时间])=1 then [实际] else 0 end) as '1月份实际',
SUM(case when MONTH([完成时间])=2 then [计划] else 0 end) as '2月份计划',
SUM(case when MONTH([完成时间])=2 then [实际] else 0 end) as '2月份实际',
SUM(case when MONTH([完成时间])=3 then [计划] else 0 end) as '3月份计划',
SUM(case when MONTH([完成时间])=3 then [实际] else 0 end) as '3月份实际',
SUM(case when MONTH([完成时间])=4 then [计划] else 0 end) as '4月份计划',
SUM(case when MONTH([完成时间])=4 then [实际] else 0 end) as '4月份实际'
from #tb group by [部门ID]
/*
部门ID 1月份计划 1月份实际 2月份计划 2月份实际 3月份计划 3月份实际 4月份计划 4月份实际
----------------------------------------------------------------------------------------------------------------
1 44 34 45 36 0 0 0 0
2 44 35 53 45 0 0 0 0
3 0 0 42 36 47 39 0 0
*/
(
ID int,
DepNO int,
planSum int,
TrueSum int,
complete dateTime
)
insert into #temb values(1,1,20,18,'2014-1-2')
insert into #temb values(2,1,24,16,'2014-1-5')
insert into #temb values(3,1,25,20,'2014-2-1')
insert into #temb values(4,1,20,16,'2014-2-4')
insert into #temb values(5,2,24,20,'2014-1-4')
insert into #temb values(6,2,20,15,'2014-1-6')
insert into #temb values(7,2,25,20,'2014-2-6')
insert into #temb values(8,2,28,25,'2014-2-8')
insert into #temb values(9,3,22,20,'2014-2-3')
insert into #temb values(10,3,20,16,'2014-2-6')
insert into #temb values(11,3,22,19,'2014-3-7')
insert into #temb values(12,3,25,20,'2014-3-9')
select a.DepNo '部门ID',sum(a.a1)'1月份计划',sum(a.a2)'1月份实际',sum(a.b1)'2月份计划',sum(a.b2)'2月份实际',sum(a.c1)'3月份计划',sum(a.c2)'3月份实际' from
(select DepNO,case when month(complete)=1 then sum(planSum) else 0 end as a1,
case when month(complete)=1 then sum(TrueSum) else 0 end as a2,
case when month(complete)=2 then sum(planSum) else 0 end as b1,
Case when month(complete)=2 then sum(TrueSum) else 0 end as b2,
case when month(complete)=3 then sum(planSum) else 0 end as c1,
case when month(complete)=3 then sum(TrueSum) else 0 end as c2
from #temb group by DepNO,month(complete))a group by a.DepNo
create table test(id int,部门id int, 计划 int,实际 int,时间 datetime)
insert test
select 1,1,20,18,'2014-1-2' union all
select 2,1,24,16,'2014-1-5' union all
select 3,1,25,20,'2014-2-1' union all
select 4,1,20,16,'2014-2-4' union all
select 5,2,24,20,'2014-1-4' union all
select 6,2,20,15,'2014-1-6' union all
select 7,2,25,20,'2014-2-6' union all
select 8,2,28,25,'2014-2-8' union all
select 9,3,22,20,'2014-2-3' union all
select 10,3,20,16,'2014-2-6' union all
select 11,3,22,19,'2014-3-7' union all
select 12,3,25,20,'2014-3-9'declare @sql varchar(5000)
set @sql='select 部门id'
select @sql=@sql+',max( case 时间 when '''+时间+''' then 计划 else 0 end)['+时间+'计划'+']'
+',max( case 时间 when '''+时间+''' then 实际 else 0 end)['+时间+'实际'+']'
from(select
distinct 时间
from (
SELECT b.部门id,
b.计划,
b.实际,
时间=CONVERT(VARCHAR(7),DATEADD(mm,A.number,'2014-01-01'), 120)
from ( select number
from master..spt_values
where type = 'P' AND number BETWEEN 0 AND 11
) a
left join
( select 部门id,
计划=SUM(计划),
实际=SUM(实际),
日期=CONVERT(varchar(7),时间,120)
from test
group by 部门id,CONVERT(varchar(7),时间,120)
) b
on CONVERT(VARCHAR(7),DATEADD(mm,a.number,'2014-01-01'), 120)=b.日期
) m
) x
set @sql=@sql+'from (SELECT b.部门id,
b.计划,
b.实际,
时间=CONVERT(VARCHAR(7),DATEADD(mm,A.number,'''+'2014-01-01'+'''), 120)
from (select number
from master..spt_values
where type = '''+'P'+''' AND number BETWEEN 0 AND 11) a
left join
(select 部门id,
计划=SUM(计划),
实际=SUM(实际),
日期=CONVERT(varchar(7),时间,120)
from test
group by 部门id,CONVERT(varchar(7),时间,120)
) b
on CONVERT(VARCHAR(7),DATEADD(mm,a.number,'''+'2014-01-01'+'''), 120)=b.日期 ) u
where 部门id is not null group by 部门id'
exec(@sql)/*
1 44 34 45 36 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 44 35 53 45 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 0 0 42 36 47 39 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
*/