Nested decode

georgethebastard used Ask the Experts™
I am trying to get this result from oracle 9i:

Job               Dept10               Dept20          Dept30           Total
ANALYST                                8000                                  6000
CLERK             1300                 1900                 950           4150
MANAGER        2450                 2975                2850          8275
PRESIDENT      5000                                                         5000
I saw an example using nested decodes that looked someting like this, but I can't seem to put it together. Thanks in advance for looking.                                                        
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

try this:

select job, sum(decode(dept,10,sal,0)) as dept10,
 sum(decode(dept,20,sal,0)) as dept20,
sum(decode(dept,30,sal,0)) as dept 30
from emp
group by job


Thank You
I appreciate this very much.
if it works for you, please accept my solution as your answer. and the other people do not need to work on this again. thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial