Originally posted by padders
Code:
SCOTT> SELECT SUBSTR (parent_region, 2, 
  2           INSTR (parent_region, '#', 2) - 2) parent_region, 
  3         child_region
  4  FROM  (SELECT SYS_CONNECT_BY_PATH (
  5                  parent_region, '#') || '#' parent_region, 
  6                child_region
  7         FROM   table_name
  8         CONNECT BY parent_region = PRIOR child_region);

PARENT_REGION                  CHILD_REGION
------------------------------ ------------------------------
CALIFORNIA                     ORANGE COUNTY
CONNECTICUT                    FAIRFIELD COUNTY
CONNECTICUT                    NORWALK
FAIRFIELD COUNTY               NORWALK
North America                  USA
North America                  CALIFORNIA
North America                  ORANGE COUNTY
North America                  CONNECTICUT
North America                  FAIRFIELD COUNTY
North America                  NORWALK
USA                            CALIFORNIA
USA                            ORANGE COUNTY
USA                            CONNECTICUT
USA                            FAIRFIELD COUNTY
USA                            NORWALK

15 rows selected.

SCOTT>
I tried executing the above query on 8.1.7 and it gives me an error saying "Invalid column name" for SYS_CONNECT_BY_PATH

Thanks
Ron