Siva Academy

Youtube.com/SivaAcademy  Oracle SQL  |  PLSQL  |  SQL PLSQL Interview Questions  |  PLSQL Scenario based Interview Questions  |  Subscriber Questions & Answers

  |  SQL practical Question  |  SQL Performance Tuning  |  New Features  |  

  Trigger     |    View  

Monday, July 8, 2019

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



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;

1 comment:

  1. Goyang Casino Hotel - Las Vegas
    Goyang https://deccasino.com/review/merit-casino/ Casino Hotel is the official name of the property for 메이피로출장마사지 its gri-go.com gaming facilities in the resort Las https://febcasino.com/review/merit-casino/ Vegas. The resort's goyangfc gaming floor, casino, and spa are

    ReplyDelete