多表查询-行列转换-合计SQL语句

create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
insert into 分类表 values(3 , '三类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
insert into 物资表 values(3 , '加热板' , 35 , 3 )
create table 部门表(部门ID int, 部门名称 varchar(10))
insert into 部门表 values(1 , 'A部门')
insert into 部门表 values(2 , 'B部门')
insert into 部门表 values(3 , 'C部门')
create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int)
insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1)
insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3)
insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2)
insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3)
insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)
insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1)
create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime)
insert into 计划表 values(1 , 1 , 2000 , '2008-10-1')
insert into 计划表 values(2 , 2 , 1000 , '2008-10-1')
insert into 计划表 values(3 , 3 , 3000 , '2008-10-1')
insert into 计划表 values(7 , 1 , 2100 , '2008-11-1')
insert into 计划表 values(8 , 2 , 1100 , '2008-11-1')
insert into 计划表 values(9 , 3 , 3100 , '2008-11-1')

declare @sql varchar(4000)
set @sql='if (object_id(''temptd1'')) is not null
drop table temptd1 '
set @sql=@sql+'
if object_id(''temptd2'') is not null
drop table temptd2 '
set @sql=@sql+'
if object_id(''temptd3'') is not null
drop table temptd3 '
set @sql=@sql+'
if object_id(''temptd4'') is not null
drop table temptd4 '
set @sql=@sql+'
select * into temptd1 from'
set @sql=@sql+'
(select b.分类名称'
select @sql=@sql+',
max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'
from (select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1 '

set @sql=@sql+'
select * into temptd2 from(select ''合计金额'' as 分类名称'
select @sql=@sql+',
sum('+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd1) 表2 '

set @sql=@sql+'
select * into temptd3 from(select ''计划金额'' as 分类名称'
select @sql=@sql+',
sum(case 部门名称 when '''+部门名称+''' then 月计划金额 else 0 end) ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3 '

set @sql=@sql+'
select * into temptd4 from(select ''差额'' as 分类名称'
select @sql=@sql+',
(temptd3.'+部门名称+'-temptd2.'+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd2,temptd3) 表4'
set @sql=@sql+'

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)'
print @sql
exec(@sql)

--打印出来的SQL静态语句
if (object_id('temptd1')) is not null
drop table temptd1
if object_id('temptd2') is not null
drop table temptd2
if object_id('temptd3') is not null
drop table temptd3
if object_id('temptd4') is not null
drop table temptd4
select * into temptd1 from
(select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1
select * into temptd2 from(select '合计金额' as 分类名称,
sum(A部门) [A部门],
sum(B部门) [B部门],
sum(C部门) [C部门] from temptd1) 表2
select * into temptd3 from(select '计划金额' as 分类名称,
sum(case 部门名称 when 'A部门' then 月计划金额 else 0 end) [A部门],
sum(case 部门名称 when 'B部门' then 月计划金额 else 0 end) [B部门],
sum(case 部门名称 when 'C部门' then 月计划金额 else 0 end) [C部门]
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3
select * into temptd4 from(select '差额' as 分类名称,
(temptd3.A部门-temptd2.A部门) [A部门],
(temptd3.B部门-temptd2.B部门) [B部门],
(temptd3.C部门-temptd2.C部门) [C部门] from temptd2,temptd3) 表4

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)

（所影响的行数为 3 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

--结果如下：
--分类名称 A部门 B部门 C部门
--二类     1200  0    600
--三类     2100  1400 0
--一类     200   0    500
--合计金额 3500  1400 1100
--计划金额 4100  2100 6100
--差额     600   700  5000

if (object_id('temptd1')) is not null
drop table temptd1
if object_id('temptd2') is not null
drop table temptd2
if object_id('temptd3') is not null
drop table temptd3
if object_id('temptd4') is not null
drop table temptd4
select * into temptd1 from
(select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1
select * into temptd2 from(select '合计金额' as 分类名称,
sum(A部门) [A部门],
sum(B部门) [B部门],
sum(C部门) [C部门] from temptd1) 表2
select * into temptd3 from(select '计划金额' as 分类名称,
sum(case 部门名称 when 'A部门' then 月计划金额 else 0 end) [A部门],
sum(case 部门名称 when 'B部门' then 月计划金额 else 0 end) [B部门],
sum(case 部门名称 when 'C部门' then 月计划金额 else 0 end) [C部门]
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3
select * into temptd4 from(select '差额' as 分类名称,
(temptd3.A部门-temptd2.A部门) [A部门],
(temptd3.B部门-temptd2.B部门) [B部门],
(temptd3.C部门-temptd2.C部门) [C部门] from temptd2,temptd3) 表4

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)