This content has been marked as final. Show 2 replies
Well, assuming you really meant to leave out Miller and Turner, then this should work ...
select c.ename, s.ename, m.ename from (select rownum rn, ename from emp where job='CLERK') c, (select rownum rn, ename from emp where job='SALESMAN') s, (select rownum rn, ename from emp where job='MANAGER') m where c.rn = s.rn and s.rn = m.rn
select max(decode(job,'CLERK' ,ENAME)) as CLERK, max(decode(job,'SALESMAN',ENAME)) as SALESMAN, max(decode(job,'MANAGER' ,ENAME)) as MANAGER from (select ENAME,job, Row_Number() over(partition by job order by ENAME) as Rn from scott.emp where job in('CLERK','SALESMAN','MANAGER')) group by Rn having count(*) = 3 order by Rn; CLERK SALESMAN MANAGER ------ -------- ------- ADAMS ALLEN BLAKE JAMES MARTIN CLARK MILLER TURNER JONES