Converting rows into columns using pivot function

we can convert rows into columns using the pivot function

let us consider the following data as sample data

DEPTNO ENAME JOB

10 ALLEN ANALYST

10 JONES CLERK

10 FORD MANAGER

10 ABCD PRESIDENT

10 CLARK SALESMAN

20 MILLER ANALYST

20 SMITH CLERK

20 WARD MANAGER

20 efgh PRESIDENT

20 MARTIN SALESMAN

30 SCOTT ANALYST

30 TURNER CLERK

30 ADAMS MANAGER

30 BLAKE PRESIDENT

30 KING SALESMAN

now create a table as emp_jobs with this data

using pivot function

we are converting rows into columns

select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));

the output is in the form

DEPTNO PRESIDENT' MANAGER' CLERK' ANALYST' SALESMAN'

20 efgh WARD SMITH MILLER MARTIN

10 ABCD FORD JONES ALLEN CLARK

30 BLAKE ADAMS TURNER SCOTT KING

but while using pivot function aggregate function should be used 

we can also achieve this conversion without using pivot function using this sql query

 select

   a1.deptno,

   a1.ename  AS PRESIDENT,

   a2.ename AS MANAGER,

   a3.ename AS CLERK

from

   emp_jobs  a1,

   emp_jobs a2,

   emp_jobs  a3

   where

   a1.deptno=a2.deptno

   and a2.deptno=a3.deptno

   and a3.deptno=a1.deptno

   and a1.job = 'PRESIDENT'

   and a2.job = 'MANAGER'

   and a3.job ='CLERK';