Query to find folder path and leaf node ID

Question

I have table as

I want to create SQl or Procedure to fetch values as below

 FolderPath          LeafFolderID 

 Dept/CSE/Cfolder     100
 Dept/Mech            10
 Team/HR              22
 Settlement           3

Please let me know how to achieve this?

You need a recursive/hierarchical query. What version of Oracle are you using? — Gordon Linoff, Mar 07 '19 at 16:13

score 1 · Accepted Answer · answered Mar 07 '19 at 16:18

1

This connect by does the job:

select folderid, ltrim(sys_connect_by_path(foldername, '/'), '/') path
  from t
  where connect_by_isleaf = 1
  connect by folderparentid = to_char(prior folderid)
  start with folderparentid = 'Root'

^demo

answered Mar 07 '19 at 16:18

Ponder Stibbons

14,723
2
21
24

Query to find folder path and leaf node ID

1 Answers1