Mi solución se divide en varios pasos lógicos:
El primer CTE llamado MiTabla simplemente simula la tabla subyacente con los datos que has dado de ejemplo.
Dado que no parece haber un campo de tipo secuencial que pueda servir para ordenar de manera fácil los datos, lo primero que hago es crear al vuelo (en el primer CTE llamado MiTablaOrdenada) un campo secuencial (de 1 a N) con el orden que deben tener las filas. Mi sugerencia es, si tu tabla subyacente tiene algún campo que sirva para este efecto, utilizarlo directamente.
Luego, en el CTE BaseDescripcion encuentro todas las filas que servirán para colocar la descripción a utilizar en la columna Fix, además aprovecho para encontrar el Número de fila del registro anterior y número de fila del registro siguiente. Para que el algoritmo funcione de manera independiente con cada nuevo id en la tabla subyacente hago particiones en la función de ventana por el campo id. Esto hará que el motor deje en NULL el campo FilaAnterior para cada primera aparición del valor TP0X en cada nuevo id, lo que es vital para construir la solución.
Finalmente, armo la solución en el CTE AsignaFix haciendo la unión de las siguientes consultas:
Obtengo las filas con cod TP0X directamente desde el CTE BaseDescripcion, asignando la descripción misma al campo Fix. Además me traigo la columna NumeroFila que servirá para ordenar el resultado final.
Obtengo las filas que están después de cada fila con cod TP0X, haciendo un inner join de BaseDescripcion con MiTablaOrdenada obteniendo todas las filas que pertenecen al mismo id y están entre el NumeroFila de la fila base de la descripción y el NumeroFilaSiguiente (sin incluirlos), y asignando la descripcion obtenida de BaseDescripcion al campo Fix.
Finalmente obtengo las filas que, para cada id, están antes de la primera aparición del cod TP0X, también con un inner join entre las mismas tablas, solamente ajustando la condición. De manera análoga asigno la descripcion obtenida de BaseDescripcion al campo Fix.
En código, se ve así:
with
MiTabla as (
select '001' id, to_date('2020/01/01', 'yyyy/mm/dd') fecha, 'TP01' cod, 'fruta' descripcion from dual
union all select '001', to_date('2020/01/02', 'yyyy/mm/dd'), 'TP0X', 'comida' from dual
union all select '001', to_date('2020/01/03', 'yyyy-mm-dd'), 'TP03', 'verdura' from dual
union all select '001', to_date('2020/01/04', 'yyyy-mm-dd'), 'TP06', 'bebestible' from dual
union all select '001', to_date('2020/01/05', 'yyyy-mm-dd'), 'TP0X', 'licor' from dual
union all select '001', to_date('2020/01/05', 'yyyy-mm-dd'), 'TP10', 'legumbre' from dual
union all select '002', to_date('2020/01/03', 'yyyy-mm-dd'), 'TP03', 'verdura' from dual
union all select '002', to_date('2020/01/08', 'yyyy-mm-dd'), 'TP0X', 'comida' from dual
union all select '002', to_date('2020/01/09', 'yyyy-mm-dd'), 'TP0X', 'licor' from dual
union all select '002', to_date('2020/01/02', 'yyyy-mm-dd'), 'TP10', 'legumbre' from dual
)
,
MiTablaOrdenada as (
select row_number() over (order by id, fecha, cod) as NumeroFila
, id
, fecha
, cod
, descripcion
from MiTabla
)
,
BaseDescripcion as (
select lag(NumeroFila) over (partition by id order by NumeroFila) FilaAnterior
, NumeroFila
, lead(NumeroFila) over (partition by id order by NumeroFila) FilaSiguiente
, id
, fecha
, cod
, Descripcion
from MiTablaOrdenada
where Cod = 'TP0X'
)
,
AsignaFix as (
select NumeroFila, id, fecha, cod, Descripcion, Descripcion Fix
from BaseDescripcion
union all
select b.NumeroFila, b.id, b.fecha, b.cod, b.Descripcion, a.descripcion
from BaseDescripcion a
inner join MiTablaOrdenada b on b.id = a.id and b.NumeroFila > a.NumeroFila and b.NumeroFila < coalesce(a.FilaSiguiente, 999999999)
union all
select b.NumeroFila, b.id, b.fecha, b.cod, b.Descripcion, a.descripcion
from BaseDescripcion a
inner join MiTablaOrdenada b on b.id = a.id and b.NumeroFila < a.NumeroFila
where a.FilaAnterior is null
)
select *
from AsignaFix
order by NumeroFila
El resultado obtenido es:
NUMEROFILA ID FECHA COD DESCRIPCION FIX
=====================================================================
1 001 01-JAN-20 TP01 fruta comida
2 001 02-JAN-20 TP0X comida comida
3 001 03-JAN-20 TP03 verdura comida
4 001 04-JAN-20 TP06 bebestible comida
5 001 05-JAN-20 TP0X licor licor
6 001 05-JAN-20 TP10 legumbre licor
7 002 02-JAN-20 TP10 legumbre comida
8 002 03-JAN-20 TP03 verdura comida
9 002 08-JAN-20 TP0X comida comida
10 002 09-JAN-20 TP0X licor licor
Puedes probar la solución en el sitio de Oracle en vivo.
Aviso de edición
La respuesta ha sido adaptada a raíz de que la pregunta fue editada por el AP. La edición incluye el caso de contar con diversos id's en los datos de entrada y que la lógica explicada por el AP aplica de manera independiente para cada id.
La solución original se encuentra aún disponible en oracle live.
