oracle - show the hierarchy of tree by showing path to leaf node -

i want write query in oracle can tree hierarchy in 1 row , last node path shown have id , parent id.

id                parent_id 1                 null 2                 1 3                 2 4                 3 5                 4 6                 5 

and output should in 1 row path

1-2-3-4-5-6 

use function sys_connect_by_path() , pseudocolumn connect_by_is_leaf:

select id, ltrim(sys_connect_by_path(id, '-'), '-') path   test connect_by_isleaf = 1   connect prior id = parent_id   start parent_id null 

output , sqlfiddle:

id   path ---  ---------------   6  1-2-3-4-5-6 

---

edit: where clause natural here, reason absolutely don't want it. use:

select id, path (   select id, ltrim(sys_connect_by_path(id, '-'), '-') path, connect_by_isleaf leaf     test connect prior id = parent_id     start parent_id null)   connect 1=0 start leaf = 1