Convert adjacency list to enumeration path ORACLE-CodePudding

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/

CodePudding user response：

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>

CodePudding user response：

Without data it's difficult to say, but if you've got data similar to

ID    PARENT_ID    NAME
1     NULL         TOP
2     1            L2
3     2            L3
4     3            BOTTOM

and you're looking for something like

TOP -> L2 -> L3 -> BOTTOM

you could use a query such as

SELECT LISTAGG(NAME, ' -> ') WITHIN GROUP(ORDER BY LEVEL) AS PATH
  FROM ADJLIST
  CONNECT BY PARENT_ID = PRIOR ID
  START WITH ID = (SELECT ID FROM ADJLIST WHERE PARENT_ID IS NULL)

db<>fiddle here