#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    5
    Rep Power
    0

    How to display 2nd row value of each job as a column value


    Hi,

    Output from a query is

    JOB ENAME
    --------- ----------
    ANALYST SCOTT
    ANALYST FORD
    CLERK SMITH
    CLERK ADAMS
    CLERK MILLER
    CLERK JAMES
    MANAGER JONES
    MANAGER CLARK
    MANAGER BLAKE
    PRESIDENT KING
    SALESMAN ALLEN
    SALESMAN MARTIN
    SALESMAN TURNER
    SALESMAN WARD

    I wanted output like below,

    JOB ENAME1 ENAME2
    --------- ---------- --------
    ANALYST SCOTT FORD
    CLERK SMITH ADAMS
    MANAGER JONES CLARK
    PRESIDENT KING
    SALESMAN ALLEN MARTIN


    ENAME1 - First row value of each job
    ENAME2 - Second row value of each job

    Ignore if it has more than 2 rows for each job, and if it has only 1 row, put NULL into ENAME2 col.

    Thanx in advance

    vijay
    Last edited by iamvijay; February 18th, 2004 at 03:10 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    Here you go:

    create table tst (

    job varchar(40),
    ename varchar(40)

    )


    insert into tst values ('ANALYST','SCOTT')
    insert into tst values ('ANALYST','FORD')
    insert into tst values ('CLERK','SMITH')
    insert into tst values ('CLERK','ADAMS')
    insert into tst values ('CLERK','MILLER')
    insert into tst values ('CLERK','JAMES')
    insert into tst values ('MANAGER','JONES')
    insert into tst values ('MANAGER','CLARK')
    insert into tst values ('MANAGER','BLAKE')
    insert into tst values ('PRESIDENT','KING')
    insert into tst values ('SALESMAN','ALLEN')
    insert into tst values ('SALESMAN','MARTIN')
    insert into tst values ('SALESMAN','TURNER')
    insert into tst values ('SALESMAN','WARD')



    select job,
    max(decode(n,1,ename,null)),
    max(decode(n,2,ename,null))
    from
    (
    select * from
    (select job, ename, row_number() over (partition by job order by job) n from tst)
    where n <= 2
    )
    group by job

    The results are:

    JOB MAX(DECODE(N,1,ENAME,NULL)) MAX(DECODE(N,2,ENAME,NULL))

    ANALYST ---- SCOTT ---- FORD
    CLERK ---- SMITH ---- ADAMS
    MANAGER ---- JONES ---- CLARK
    PRESIDENT ---- KING ---- (nil)
    SALESMAN ---- ALLEN ---- MARTIN

    Regards,
    Dan

    btw, is it work or studies ?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    5
    Rep Power
    0

    Thanq very much,


    Thanq Dan,

    it is working,

    and btw, this is not for study purpose,

    thanx a lot

    vijay

IMN logo majestic logo threadwatch logo seochat tools logo