create table emp1(empno number, ename varchar2(100));
create table emp2(empno1 number, ename1 varchar2(100),
empno2 number, ename2 varchar2(100));
insert into emp1 values(1,'KING');
insert into emp1 values(2,'BLAKE');
insert into emp1 values(3,'CLARK');
insert into emp1 values(4,'JONES');
insert into emp1 values(5,'SCOTT');
insert into emp1 values(6,'FORD');
insert into emp1 values(7,'SMITH');
insert into emp1 values(8,'ALLEN');
insert into emp1 values(9,'WARD');
insert into emp1 values(10,'MARTIN');
insert into emp1 values(11,'SIVA');
commit;
------------------
insert into emp2
with d as (select empno,ename,
round(count(1) over()/2) - (row_number() over(order by empno)) r
from emp1),
d1 as (select empno,ename,r
from d
where r >=0),
d2 as (select empno,ename,abs(r) r
from d
where r <0)
select d1.empno,d1.ename,d2.empno,d2.ename
from d1 full outer join d2
on (d1.r = d2.r)
order by d1.r desc;
------------------
insert into emp2
with d as (select empno,ename,
round(count(1) over()/2) - (row_number() over(order by empno)) r
from emp1),
d1 as (select empno,ename,r
from d
where r >=0),
d2 as (select empno,ename,abs(r) r
from d
where r <0)
select d1.empno,d1.ename,d2.empno,d2.ename
from d1 left outer join d2
on (d1.r = d2.r)
order by d1.r desc;
