select * from ( select [所属排水口编号],[时间],[普通水],[污水] from A pivot ( sum([数据值]) for [类别] in ([普通水],[污水]) ) pvt ) a inner join ( select [所属排水口编号],[时间],[COD],[氨氮] from B pivot ( sum([数据值]) for [类别] in ([COD],[氨氮]) ) pvt ) b on a.[所属排水口编号]=b.[所属排水口编号] and a.[时间]=b.[时间] inner join TB on a.[所属排水口编号]=TB.[编号]
好久没来了,求分。--建表create table a(id int,类别 varchar(20),时间 datetime,数据值 int,所属排水口编号 varchar(20)) insert a select 1,'普通水','2013-09-06 10:05:00',1000,'10001' union all select 2,'污水','2013-09-06 10:05:00',500,'10001' union all select 3,'普通水','2013-09-06 10:10:00',900,'10001' union all select 4,'污水','2013-09-06 10:10:00',400,'10001'create table b(id int,类别 varchar(20),时间 datetime,数据值 int,所属排水口编号 varchar(20)) insert b select 1, 'COD','2013-09-06 10:05:00', 40,'10001' union all select 2, '氨氮','2013-09-06 10:05:00', 5,'10001' union all select 3, 'COD','2013-09-06 10:05:00', 50,'10002' union all select 4, '氨氮','2013-09-06 10:05:00', 4,'10002'create table tb(id int,编号 varchar(20),名称 varchar(50)) insert tb select 1,'10001','XX污水厂1号口' union all select 2,'10002','XX污水厂2号口'--按类别组合动态语句 declare @sql varchar(max)select @sql ='select tb.编号,tb.名称,c.时间,'select @sql = @sql + ' sum(case when 类别=''' + 类别 + ''' then 数据值 else 0 end) as ' + 类别 + ',' from tb left join (select 1 as type,* from a union all select 2 as type,* from b) c on c.所属排水口编号 = tb.编号 where isNull(类别,'') <> '' group by 类别,type order by type select @sql = @sql + 'Cast(100 * cast(sum(case when 类别=''污水'' then 数据值 else 0 end) as dec(10,4)) / Case when sum(case when 类别 in (''普通水'',''污水'') then 数据值 else 0 end) = 0 then 1 else sum(case when 类别 in (''普通水'',''污水'') then 数据值 else 0 end) end as dec(10,2)) as 污水排放率 from tb left join ( select * from a union all select * from b) c on c.所属排水口编号 = tb.编号 group by tb.编号,tb.名称,c.时间 '--输出动态语句 print @sql --执行动态语句 exec(@sql) --删除测试表 drop table tb,a,b/*结果编号 名称 时间 普通水 污水 COD 氨氮 污水排放率 10001 XX污水厂1号口 2013-09-06 10:05:00.000 1000 500 40 5 33.33 10001 XX污水厂1号口 2013-09-06 10:10:00.000 900 400 0 0 30.77 10002 XX污水厂2号口 2013-09-06 10:05:00.000 0 0 50 4 0.00 */
http://blog.csdn.net/hdhai9451/article/details/5026933
select * from
(
select [所属排水口编号],[时间],[普通水],[污水] from A
pivot
(
sum([数据值]) for [类别] in ([普通水],[污水])
) pvt
) a
inner join
(
select [所属排水口编号],[时间],[COD],[氨氮] from B
pivot
(
sum([数据值]) for [类别] in ([COD],[氨氮])
) pvt
) b
on a.[所属排水口编号]=b.[所属排水口编号] and a.[时间]=b.[时间]
inner join TB
on a.[所属排水口编号]=TB.[编号]
insert a
select 1,'普通水','2013-09-06 10:05:00',1000,'10001'
union all
select 2,'污水','2013-09-06 10:05:00',500,'10001'
union all
select 3,'普通水','2013-09-06 10:10:00',900,'10001'
union all
select 4,'污水','2013-09-06 10:10:00',400,'10001'create table b(id int,类别 varchar(20),时间 datetime,数据值 int,所属排水口编号 varchar(20))
insert b
select 1, 'COD','2013-09-06 10:05:00', 40,'10001'
union all
select 2, '氨氮','2013-09-06 10:05:00', 5,'10001'
union all
select 3, 'COD','2013-09-06 10:05:00', 50,'10002'
union all
select 4, '氨氮','2013-09-06 10:05:00', 4,'10002'create table tb(id int,编号 varchar(20),名称 varchar(50))
insert tb
select 1,'10001','XX污水厂1号口'
union all
select 2,'10002','XX污水厂2号口'--按类别组合动态语句
declare @sql varchar(max)select @sql ='select tb.编号,tb.名称,c.时间,'select @sql = @sql + '
sum(case when 类别=''' + 类别 + ''' then 数据值 else 0 end) as ' + 类别 + ','
from tb left join
(select 1 as type,* from a union all select 2 as type,* from b) c on c.所属排水口编号 = tb.编号
where isNull(类别,'') <> ''
group by 类别,type order by type
select @sql = @sql + 'Cast(100 * cast(sum(case when 类别=''污水'' then 数据值 else 0 end) as dec(10,4)) /
Case when sum(case when 类别 in (''普通水'',''污水'') then 数据值 else 0 end) = 0 then 1 else sum(case when 类别 in (''普通水'',''污水'') then 数据值 else 0 end) end as dec(10,2)) as 污水排放率
from tb
left join
( select * from a union all select * from b) c on c.所属排水口编号 = tb.编号
group by tb.编号,tb.名称,c.时间 '--输出动态语句
print @sql
--执行动态语句
exec(@sql)
--删除测试表
drop table tb,a,b/*结果编号 名称 时间 普通水 污水 COD 氨氮 污水排放率
10001 XX污水厂1号口 2013-09-06 10:05:00.000 1000 500 40 5 33.33
10001 XX污水厂1号口 2013-09-06 10:10:00.000 900 400 0 0 30.77
10002 XX污水厂2号口 2013-09-06 10:05:00.000 0 0 50 4 0.00
*/