Originally Posted by
bhaski
hi check it
10:27:21 SQL> select
10:27:23 2 empno,empname,
10:27:23 3 max(decode(rn, 1, jobname))||'/' col1,
10:27:23 4 max(decode(rn, 2, jobname)) col2
10:27:23 5 from (
10:27:23 6 select empno,empname,jobname, row_number() over (partition by empno order by empno) rn
10:27:23 7 from ab
10:27:23 8 order by empno)
10:27:23 9 group by empno,empname;
EMPNO EMPNAME COL1 COL2
---------- -------------------- ----------- ----------
0001 Roger Programer/ Analyst
0002 Miriam Developer/ Analyst
0003 Roman Encoder/
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'AB' (TABLE)
10:27:25 SQL> select *from ab;
EMPNO EMPNAME JOBNAME
---------- -------------------- ----------
0001 Roger Programer
0002 Miriam Developer
0001 Roger Analyst
0003 Roman Encoder
0002 Miriam Analyst
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'AB' (TABLE)