select dept DEPT,
sum(decode(job,'经理',avg_sal,0)) 经理,
sum(decode(job,'主管',avg_sal,0)) 主管,
sum(decode(job,'干事',avg_sal,0)) 干事,
sum(decode(job,'文员',avg_sal,0)) 文员
from table
group by dept
sum(decode(job,'经理',avg_sal,0)) 经理,
sum(decode(job,'主管',avg_sal,0)) 主管,
sum(decode(job,'干事',avg_sal,0)) 干事,
sum(decode(job,'文员',avg_sal,0)) 文员
from table
group by dept
(select DEPT, DECODE(job, '经理', avg_sal) 经理, DECODE(job, '主管', 0) 主管, DECODE(job, '干事',0) 干事,DECODE(job, '文员',0) 文员 from abc
union
select DEPT, DECODE(job, '经理', 0) 经理, DECODE(job, '主管', avg_sal) 主管, DECODE(job, '干事',0) 干事,DECODE(job, '文员',0) 文员 from abc
union
select DEPT, DECODE(job, '经理', 0) 经理, DECODE(job, '主管', 0) 主管, DECODE(job, '干事',avg_sal) 干事,DECODE(job, '文员',0) 文员 from abc
union
select DEPT, DECODE(job, '经理', 0) 经理, DECODE(job, '主管', 0) 主管, DECODE(job, '干事',0) 干事,DECODE(job, '文员',avg_sal) 文员 from abc
)
group by dept