Consolidate two rows as single row

Consolidate two rows as single row

  • First and last records of table1 into table2 has to be single row
  • Second and last but second row of table1 into table2

input

OUTPUT should be

Employee1 data insert into employee2 like this

Logic query

logic query
select empno,ename,row_number() over(order by empno) r,
count(*) over() c,count(*) over()/2 c1,round(count(1) over()/2)c2,
round(count(1) over()/2) - row_number() over(order by empno) rc
from employee1

logic output

Here RC=C2-R means

at EMPNO=101 RC=4-1

EMPNO=102 2=4-2 … so on

Final query

with d as (select empno,ename,round(count(*) over()/2) - row_number() over(order by empno)rc from employee1),
d1 as(select empno,ename,rc from d where rc>=0),
d2 as(select empno,ename,abs(rc) rc from d where rc<0)
select d1.empno,d1.ename,d2.empno,d2.ename from d1,d2
where d1.rc=d2.rc(+)       --(+)left outer join using for empno104 (it is extra for this table)
order by d1.empno

Final Output will be

Leave a Reply