SQL Route Finder in Oracle - Recursion?

For a single given starting position, this will (I think.. Sorry, typing by hand on an iPad) provide a row for each route that leaves that starting point.

  SELECT    LEVEL as route_step,    t1.next_hop_station as next_station,    t1.stageid  FROM     stage t1    INNER  JOIN stage t2     ON t2.start_station = t1.next_hop_station  START WITH    t1.start_station = 'your start station'  CONNECT BY     PRIOR t1.start_station = t1.next_hop_station

So, for start station Penzance:

Route_Step  Next_Station StageID1.          Plymouth.    12.          Exeter.      23.          Taunton.     34.          Reading.     95.          Basingstoke. 106.          Southampton  67.          Poole.       78.          Weymouth     85.          Paddington.  113.          Salisbury    44.          Basingstoke. 55.          Southampton. 66.          Poole.       77.          Weymouth.    8* excuse the .'s!

Wrapping that with a join on your distinct starting stations (and removing the explicit START WITH clause so that you get routes from all stations, not just a single station) will give you what you need for your output table (although as per previous comments, I'm not sure what use that structure is to you, as you lose pertinent detail):

SELECT     First_Stage.stageid as routeid,     q.stageid   FROM   (      SELECT        LEVEL as route_step,        t1.next_hop_station as next_station,        t1.stageid      FROM         stage t1        INNER  JOIN stage t2         ON t2.start_station = t1.next_hop_station      CONNECT BY         PRIOR t1.start_station = t1.next_hop_station    ) q    INNER JOIN stage as first_stage    ON first_stage.stageid = q.stageid    AND q.route_step = 1