Oracle query to show change of status in each row with previous status

Question

I have a table with following rows

Need to write an oracle sql query to get the output as:

P.S: I am using Oracle 9i. Let me know if this can be done by just using oracle 9i sql.

Answer 1

You can use lag():

select proj_name, prev_status || ' to ' || status
from (select t.*, lag(status) over (partition by proj_name order by date) as prev_status
      from t
     ) t
where prev_status is not null;