Insert two rows from one table into single row of another table

 

 

 

drop table emp1;

drop table emp2;

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;

 

 

select empno,ename,

row_number() over(order by empno),

count(1) over() c

from emp1;

 

select empno,ename,

row_number() over(order by empno),

count(1) over() c,

round(count(1) over()/2) c

from emp1;

   

    select empno,ename,

row_number() over(order by empno) r,

round(count(1) over()/2) c,

round(count(1) over()/2)-row_number() over(order by empno) d

from emp1;

 

 with d as (select empno,ename,

round(count(1) over()/2)-row_number() over(order by empno) c

from emp1),

d1 as (select empno,ename,c from d where c>=0),

d2 as (select empno,ename,abs(c) c from d where c<0)

select * from d1,d2 where d1.c=d2.c(+);

 

 with d as (select empno,ename,

round(count(1) over()/2)-row_number() over(order by empno) c

from emp1),

d1 as (select empno,ename,c from d where c>=0),

d2 as (select empno,ename,abs(c) c from d where c<0)

select d1.empno,d1.ename,d2.empno,d2.ename from d1,d2 where d1.c=d2.c(+);

 

insert into emp2

 with d as (select empno,ename,

round(count(1) over()/2)-row_number() over(order by empno) c

from emp1),

d1 as (select empno,ename,c from d where c>=0),

d2 as (select empno,ename,abs(c) c from d where c<0)

select d1.empno,d1.ename,d2.empno,d2.ename from d1,d2 where d1.c=d2.c(+)

order by d1.empno ;

 

select * from emp2;