SQL> select deptno, job from emp order by 1, 2;
    DEPTNO JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 ANALYST
        20 CLERK
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN
        30 SALESMAN
        30 SALESMAN
        30 SALESMAN

14 rows selected.

SQL> select deptno,
  2         count(decode(job,'CLERK',job)) clerk,
  3         count(decode(job,'SALESMAN',job)) salesman,
  4         count(decode(job,'MANAGER',job)) manager,
  5         count(decode(job,'PRESIDENT',job)) president
  6  from emp
  7  group by deptno
  8  order by deptno
  9  /
    DEPTNO      CLERK   SALESMAN    MANAGER  PRESIDENT
---------- ---------- ---------- ---------- ----------
        10          1          0          1          1
        20          2          0          1          0
        30          1          4          1          0

3 rows selected.

SQL> select *
  2  from (select deptno, job from emp)
  3       pivot(count(job)
  4             for job in ('CLERK' clerk,
  5                         'SALESMAN' salesman,
  6                         'MANAGER' manager,
  7                         'PRESIDENT' president))
  8  order by deptno
  9  /
    DEPTNO      CLERK   SALESMAN    MANAGER  PRESIDENT
---------- ---------- ---------- ---------- ----------
        10          1          0          1          1
        20          2          0          1          0
        30          1          4          1          0

3 rows selected.