看下面的表结构：
| Category | 设备类型 | String |
| DeviceNum | 设备数量 | Number |
| Area | 区域 | String |
查询结果如下：
现在要显示成下图的效果就要使用普通的行转列语句。
sql语句：
1 select AREA,
2 sum(decode(category,'2G基站',devicenum,null)) "2G",
3 sum(decode(category,'3G基站',devicenum,null)) "3G",
4 sum(decode(category,'总计',devicenum,null)) "ALL"
5 from MV_CUTOVER_BASESITE_CAPACITY
6 group by AREA
查询结果为：
上面的行转列比较简单，再看下面的表结构：
查询结果为：
现在需要达到下图这种效果：
这里的普通行转列不能完成，需要union all来连接：
1 select '已办工单' "TYPE", a.* from (
2 select
3 sum(nvl(decode(area,'杭州',inprogressnum,null),0)) "HANGZHOU" ,
4 sum(nvl(decode(area,'湖州',inprogressnum,null),0)) "HUZHOU" ,
5 sum(nvl(decode(area,'金华',inprogressnum,null),0)) "JINHUA" ,
6 sum(nvl(decode(area,'宁波',inprogressnum,null),0)) "NINGBO" ,
7 sum(nvl(decode(area,'全省',inprogressnum,null),0)) "ALL"
8 from mv_service_order
9 ) a, dual b
10 union all
11 select '超时工单' "TYPE", a.* from (
12 select
13 sum(nvl(decode(area,'杭州',achivenum,null),0)) "HANGZHOU" ,
14 sum(nvl(decode(area,'湖州',achivenum,null),0)) "HUZHOU" ,
15 sum(nvl(decode(area,'金华',achivenum,null),0)) "JINHUA" ,
16 sum(nvl(decode(area,'宁波',achivenum,null),0)) "NINGBO" ,
17 sum(nvl(decode(area,'全省',achivenum,null),0)) "ALL"
18 from mv_service_order
19 )a, dual b
20 union all
21 select '已归档工单' "TYPE",a.* from(
22 select
23 sum(nvl(decode(area,'杭州',delaynum,null),0)) "HANGZHOU" ,
24 sum(nvl(decode(area,'湖州',delaynum,null),0)) "HUZHOU" ,
25 sum(nvl(decode(area,'金华',delaynum,null),0)) "JINHUA" ,
26 sum(nvl(decode(area,'宁波',delaynum,null),0)) "NINGBO" ,
27 sum(nvl(decode(area,'全省',delaynum,null),0)) "ALL"
28 from mv_service_order
29 )a,dual b
30 union all
31 select '工单总量' "TYPE",a.* from(
32 select
33 sum(nvl(decode(area,'杭州',totalnum,null),0)) "HANGZHOU" ,
34 sum(nvl(decode(area,'湖州',totalnum,null),0)) "HUZHOU" ,
35 sum(nvl(decode(area,'金华',totalnum,null),0)) "JINHUA" ,
36 sum(nvl(decode(area,'宁波',totalnum,null),0)) "NINGBO" ,
37 sum(nvl(decode(area,'全省',totalnum,null),0)) "ALL"
38 from mv_service_order
39 )a, dual b
查询结果如下：
