By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with sample as (
select 1 id, 'Doris' student union all
select 2 id, 'Abbot' union all
select 3 id, 'Green' union all
select 4 id, 'Emerson' union all
select 5 id, 'Jeames'
)
select s1.id, coalesce(s2.student,s1.student)
from sample s1
left join sample s2
on s1.id + case when s1.id%2=1 then 1 else -1 end = s2.id
| id | coalesce(s2.student,s1.student) |
|---|---|
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |