一个交叉报表问题,请高手指教!

[tinderman]

有如下表:

日期 部门编号 A数量 B数量 C数量 D数量
2006-9-1 1 10 20 30 40
2006-9-1 2 10 20 30 40
2009-9-2 1 10 20 30 40
2006-9-2 2 10 20 30 40
2006-10-1 1 10 20 30 40
2006-10-1 2 10 20 30 40
2006-10-2 1 10 20 30 40
2006-10-2 2 10 20 30 40

想得到如下结果:

部门1 部门2 合计
日期 |A B C D |A B C D |A B C D
2006-9-1 |10 20 30 40 |10 20 30 40 |20 40 60 80
2006-9-2 |10 20 30 40 |10 20 30 40 |20 40 60 80
小计 |20 40 60 80 |20 40 60 80 |40 80 120 160
2006-10-1 |10 20 30 40 |10 20 30 40 |20 40 60 80
2006-10-2 |10 20 30 40 |10 20 30 40 |20 40 60 80
小计 |20 40 60 80 |20 40 60 80 |40 80 120 160
合计 |40 80 120 160|40 80 120 160 |80 160 240 320

[pengda1i]

复杂格式是客户端的任务

[子陌红尘]

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

[子陌红尘]

输出结果：

/*
日期 部门1-A 部门1-B 部门1-C 部门1-D 部门2-A 部门2-B 部门2-C 部门2-D 合计-A 合计-B 合计-C 合计-D
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2006-09-01 10 20 30 40 10 20 30 40 20 40 60 80
2006-09-02 10 20 30 40 10 20 30 40 20 40 60 80
小计 20 40 60 80 20 40 60 80 40 80 120 160
2006-10-01 10 20 30 40 10 20 30 40 20 40 60 80
2006-10-02 10 20 30 40 10 20 30 40 20 40 60 80
小计 20 40 60 80 20 40 60 80 40 80 120 160
合计 40 80 120 160 40 80 120 160 80 160 240 320
*/

[pengda1i]

只能做到这个格式了

declare @sql varchar(8000)
select @sql=''

select @sql=@sql+',sum(case when 部门编号='+cast(部门编号 as varchar)+' then A else 0 end) as [部门'+cast(部门编号 as varchar)+'A]'
+',sum(case when 部门编号='+cast(部门编号 as varchar)+' then B else 0 end) as [部门'+cast(部门编号 as varchar)+'B]'
+',sum(case when 部门编号='+cast(部门编号 as varchar)+' then C else 0 end) as [部门'+cast(部门编号 as varchar)+'C]'
+',sum(case when 部门编号='+cast(部门编号 as varchar)+' then D else 0 end) as [部门'+cast(部门编号 as varchar)+'D]'
from tablename
group by 部门编号

exec('
select CASE WHEN (GROUPING(left(dt,7)) = 1) THEN ''小计''
WHEN (GROUPING(dt) = 1) THEN ''合计''
ELSE dt
END AS 日期'+@sql
+',sum(A) as [合计A],sum(B) as [合计B],sum(C) as [合计C],sum(D) as [合计D]
from (
select convert(char(10),日期,120) as dt,*
from tablename
) as t1
group by left(dt,7),dt
with ROLLUP
')


--没有测试

[tinderman]

非常感谢 子陌红尘!!!! 这正是我想要的结果,以后还得多学习.

[laowang2]

mark