Combine lead and lag function without overlap

I have this table:

COL
---
B
C
D

I need to get an output like this:

COL1          COL2
----          ----
Creation         B
       B         C
       C         D
       D   Pending

I’ve tried a normal lead

nvl(lead(col) order by col , 'Creation')

window function, but that would only show the first ‘Creation’ and not that last ‘Pending’ that I need.

Answer

Your table has N records (3 in the example provided) when query must return N + 1 (4); let’s modify the initial data in order to query N + 1 records:

  with q as (
    -- Additional null record
    select null as Col
      from dual 

     union all

    -- Added to the initial table 
    select col
      from myTable
  )

  select nvl(Lag(COL) over (order by col), 'Creation') as Col1,
         nvl(Col, 'Pending') as Col2
    from q

Fiddle