sql - How to find out below output using oracle query

Question

Input:

col1  col2
------------
1 A
2 1
3 B
4 2
5 C
6 Null

Output i want

Col1 Col2
__________
A  1
B  2
C Null

question from:https://stackoverflow.com/questions/65847744/how-to-find-out-below-output-using-oracle-query

Answer 1

Oh, I see. Assuming there are no gaps in col1, you can use aggregation using arithmetic:

select max(case when mod(id, 2) = 0 then col2 end),
       max(case when mod(id, 2) = 1 then col2 end)
from t
group by floor((id - 1) / 2);

Another method uses lead():

select col2, next_col2
from (select t.*, lead(col2) over (order by id) as next_col2
      from t
     ) t
where mod(id, 2) = 1;