Summarize in ORACLE hierarchical query

Question

I need some advise because I am very new to hierarchical queries :( I.e. I have the following table:

CREATE TABLE routes
(
  from       VARCHAR2(15),
  to         VARCHAR2(15),
  price      NUMBER
);

INSERT INTO routes VALUES('San Francisco', 'Denver', 1000);
INSERT INTO routes VALUES('San Francisco', 'Dallas', 10000);
INSERT INTO routes VALUES('Denver', 'Dallas', 500);
INSERT INTO routes VALUES('Denver', 'Chicago', 2000);
INSERT INTO routes VALUES('Dallas', 'Chicago', 600);
INSERT INTO routes VALUES('Dallas', 'New York', 2000);
INSERT INTO routes VALUES('Chicago', 'New York', 3000);
INSERT INTO routes VALUES('Chicago', 'Denver', 2000);

I want to calculate the price through the hierarchy, to get the following result:

FROM            TO              PRICE 
--------------- --------------- ----- 
San Francisco   Dallas          10000  //San Francisco -> Dallas
San Francisco   Denver          1000   //San Francisco -> Denver
San Francisco   Chicago         10600  //San Francisco --> Dallas --> Chicago  (10000 + 600)
San Francisco   New York        12000  //San Francisco --> Dallas --> New York (10000 + 200)
San Francisco   Chicago         3000   //San Francisco --> Denver --> Chicago  (1000 + 2000)
San Francisco   Dallas          1500   //San Francisco --> Denver --> Dallas   (1000 + 500)
.               .               .
.               .               .
.               .               .

I've alreary imagined, that the CONNECT BY PRIOR statement should be used and have written a query which runs through the hierarchy:

SELECT 
    CONNECT_BY_ROOT from, 
    to
  FROM routes
  CONNECT BY NOCYCLE PRIOR to = from;

I really could use some help how to get the prices.

Than You!

Answer 1

It is not the final solution but maybe a starting point. It shows price as text not as nubmer, I don't know a quick solution to sum them up.

SELECT 
    SUBSTR(SYS_CONNECT_BY_PATH(FROM||'->'||TO, '.'), 2) AS journey,
    SUBSTR(SYS_CONNECT_BY_PATH(price, '+'), 2) AS prices
FROM routes
START WITH FROM_airport = 'San Francisco' 
CONNECT BY NOCYCLE PRIOR  TO = FROM;

I selected only journes from San Francisco in order to get a better overview.

Result:

JOURNEY                                                                |PRICES
San Francisco->Dallas                                                  |10000
San Francisco->Dallas.Dallas->Chicago                                  |10000+600
San Francisco->Dallas.Dallas->Chicago.Chicago->Denver                  |10000+600+2000
San Francisco->Dallas.Dallas->Chicago.Chicago->New York                |10000+600+3000
San Francisco->Dallas.Dallas->New York                                 |10000+2000
San Francisco->Denver                                                  |1000
San Francisco->Denver.Denver->Chicago                                  |1000+2000
San Francisco->Denver.Denver->Chicago.Chicago->New York                |1000+2000+3000
San Francisco->Denver.Denver->Dallas                                   |1000+500
San Francisco->Denver.Denver->Dallas.Dallas->Chicago                   |1000+500+600
San Francisco->Denver.Denver->Dallas.Dallas->Chicago.Chicago->New York |1000+500+600+3000
San Francisco->Denver.Denver->Dallas.Dallas->New York                  |1000+500+2000

Answer 2

Much more straightforward using recursive sql (changed from/to to f/t to avoid using keywords)

CREATE TABLE routes
(
  f       VARCHAR2(15),
  t         VARCHAR2(15),
  price      NUMBER
);

INSERT INTO routes VALUES('San Francisco', 'Denver', 1000);
INSERT INTO routes VALUES('San Francisco', 'Dallas', 10000);
INSERT INTO routes VALUES('Denver', 'Dallas', 500);
INSERT INTO routes VALUES('Denver', 'Chicago', 2000);
INSERT INTO routes VALUES('Dallas', 'Chicago', 600);
INSERT INTO routes VALUES('Dallas', 'New York', 2000);
INSERT INTO routes VALUES('Chicago', 'New York', 3000);
INSERT INTO routes VALUES('Chicago', 'Denver', 2000);


with 
t1 (start_loc, current_loc, route, total_price, stops) as
  ( select  distinct f,f, cast(f as varchar2(4000)), 0, 0 
    from    routes
    union all
    select  start_loc, t, route||'->'||t, total_price+price , stops+1
    from    routes r
            join t1 on (f = current_loc and instr(route,t)=0)
  )
select  * 
from    t1
where   stops <> 0
order by    
        start_loc, current_loc, total_price ASC

RESULTS:

START_LOC      CURRENT_LOC  ROUTE                                             TOTAL_PRICE  STOPS  
-------------- ------------ ------------------------------------------------- ------------ ------
Chicago        Dallas       Chicago->Denver->Dallas                           2500         2     
Chicago        Denver       Chicago->Denver                                   2000         1     
Chicago        New York     Chicago->Denver->Dallas->New York                 4500         3     
Chicago        New York     Chicago->New York                                 3000         1     
Dallas         Chicago      Dallas->Chicago                                   600          1     
Dallas         Denver       Dallas->Chicago->Denver                           2600         2     
Dallas         New York     Dallas->Chicago->New York                         3600         2     
Dallas         New York     Dallas->New York                                  2000         1     
Denver         Chicago      Denver->Chicago                                   2000         1     
Denver         Chicago      Denver->Dallas->Chicago                           1100         2     
Denver         Dallas       Denver->Dallas                                    500          1     
Denver         New York     Denver->Chicago->New York                         5000         2     
Denver         New York     Denver->Dallas->Chicago->New York                 4100         3     
Denver         New York     Denver->Dallas->New York                          2500         2     
San Francisco  Chicago      San Francisco->Dallas->Chicago                    10600        2     
San Francisco  Chicago      San Francisco->Denver->Chicago                    3000         2     
San Francisco  Chicago      San Francisco->Denver->Dallas->Chicago            2100         3     
San Francisco  Dallas       San Francisco->Dallas                             10000        1     
San Francisco  Dallas       San Francisco->Denver->Dallas                     1500         2     
San Francisco  Denver       San Francisco->Dallas->Chicago->Denver            12600        3     
San Francisco  Denver       San Francisco->Denver                             1000         1     
San Francisco  New York     San Francisco->Dallas->Chicago->New York          13600        3     
San Francisco  New York     San Francisco->Dallas->New York                   12000        2     
San Francisco  New York     San Francisco->Denver->Chicago->New York          6000         3     
San Francisco  New York     San Francisco->Denver->Dallas->Chicago->New York  5100         4     
San Francisco  New York     San Francisco->Denver->Dallas->New York           3500         3