2 Replies Latest reply on Sep 22, 2008 12:56 PM by Aketi Jyuuzou

    SQL Query

    289018
      SQL> select ename from emp where job='CLERK';

      ENAME
      ----------
      Smith
      Adams
      James
      Miller

      SQL> select ename from emp where job='SALESMAN';

      ENAME
      ----------
      Allen
      Ward
      Martin
      Turner

      SQL> select ename from emp where job='MANAGER'
      2 /

      ENAME
      ----------
      Jones
      Blake
      Clark


      I need a SQL query to get data in this format .

      CLERK SALESMAN MANAGER
      ----- -------- ---------
      Smith Allen Jones
      Adams Ward Blake
      James Martin Clark

      thanks!
        • 1. Re: SQL Query
          24124
          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
          • 2. Re: SQL Query
            Aketi Jyuuzou
            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