select t1.city,
(select nvl(sum(话务量),0) from test t2 where t1.city=t2.city and t2.datatype=1) "TD话务量",
(select nvl(sum(话务量),0) from test t2 where t1.city=t2.city and t2.datatype=2) "GSM话务量",
(select nvl(sum(流量),0) from test t2 where t1.city=t2.city) "流量",
(select nvl(sum(视频),0) from test t2 where t1.city=t2.city) "视频"
from (select distinct t.city from test t)t1
(select nvl(sum(话务量),0) from test t2 where t1.city=t2.city and t2.datatype=1) "TD话务量",
(select nvl(sum(话务量),0) from test t2 where t1.city=t2.city and t2.datatype=2) "GSM话务量",
(select nvl(sum(流量),0) from test t2 where t1.city=t2.city) "流量",
(select nvl(sum(视频),0) from test t2 where t1.city=t2.city) "视频"
from (select distinct t.city from test t)t1
sum(decode(数据类型, '1', 话务量, 0)) TD话务量,
sum(decode(数据类型, '2', 话务量, 0)) GSM话务量,
sum(流量) 流量,
sum(视频) 视频
from t
select 城市,
sum(decode(数据类型, '1', 话务量, 0)) TD话务量,
sum(decode(数据类型, '2', 话务量, 0)) GSM话务量,
sum(流量) 流量,
sum(视频) 视频
from t
group by 城市