create table test(日期 datetime,部门编号 int,A数量 int,B数量 int,C数量 int,D数量 int)
insert into test select '2006-09-01',1,10,20,30,40
insert into test select '2006-09-01',2,10,20,30,40
insert into test select '2006-09-02',1,10,20,30,40
insert into test select '2006-09-02',2,10,20,30,40
insert into test select '2006-10-01',1,10,20,30,40
insert into test select '2006-10-01',2,10,20,30,40
insert into test select '2006-10-02',1,10,20,30,40
insert into test select '2006-10-02',2,10,20,30,40
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case 部门编号 when '+rtrim(部门编号)+' then A数量 else 0 end) as [部门'+rtrim(部门编号)+'-A]'
+',sum(case 部门编号 when '+rtrim(部门编号)+' then B数量 else 0 end) as [部门'+rtrim(部门编号)+'-B]'
+',sum(case 部门编号 when '+rtrim(部门编号)+' then C数量 else 0 end) as [部门'+rtrim(部门编号)+'-C]'
+',sum(case 部门编号 when '+rtrim(部门编号)+' then D数量 else 0 end) as [部门'+rtrim(部门编号)+'-D]'
from test group by 部门编号
set @sql= 'select (case when grouping(月份)=1 then ''合计'' when grouping(日期)=1 then ''小计'' else 日期 end) as 日期'+@sql+',[合计-A]=sum(A数量),[合计-B]=sum(B数量),[合计-C]=sum(C数量),[合计-D]=sum(D数量) '
+' from (select convert(char(7),日期,120) as 月份,convert(char(10),日期,120) as 日期,部门编号,A数量,B数量,C数量,D数量 from test) t group by 月份,日期'
+' with rollup'
print @sql
exec(@sql)
go
drop table test
go
