How can I create a procedure that converts an adjacency list to enumeration path in Oracle (by creating a new table as an enumeration path)? The adjacency list is a table that looks like this:
AdjList(id number, parent_id number, name varchar2)
ID PARENT_ID NAME
-----------------------------
1 CATEGORY1
2 1 PROD1
3 CATEGORY2
4 3 PROD2
5 4 SUBPROD1
Desired output:
EnumPath(id number, parent_id number, name varchar2, path varchar2)
ID PARENT_ID NAME PATH
-----------------------------------
1 CATEGORY1 /1/
2 1 PROD1 /1/2/
3 CATEGORY2 /3/
4 3 PROD2 /3/4/
5 4 SUBPROD1 /3/4/5/
Looks like a sys_connect_by_path
hierarchical query to me.
SQL> with test (id, parent_id, name) as
2 (select 1, null, 'category1' from dual union all
3 select 2, 1 , 'prod1' from dual union all
4 select 3, null, 'category2' from dual union all
5 select 4, 3 , 'prod2' from dual union all
6 select 5, 4 , 'subprod1' from dual
7 )
8 select id, parent_id, name,
9 ltrim(sys_connect_by_path(id, '>'), '>') path
10 from test
11 connect by prior id = parent_id
12 start with parent_id is null;
ID PARENT_ID NAME PATH
---------- ---------- --------- --------------------
1 category1 1
2 1 prod1 1>2
3 category2 3
4 3 prod2 3>4
5 4 subprod1 3>4>5
SQL>