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

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 ?

Thanq Dan,

it is working,

and btw, this is not for study purpose,

thanx a lot

vijay