Deriving Outline Numbering based on CONNECT BY hierarchy

Question

I desire to create an automatically numbered outline based on a CONNECT BY query result.

IF my query results are as follows:

level col1
----- --------
1     text1
1     text2
2     text3
3     text4
3     text5
1     text6

I am interested in deriving the numeric hierarchy values like so:

level outline col1
----- ------- --------
1     1       text1
1     2       text2
2     2.1     text3
3     2.1.1   text4
3     2.1.2   text5
1     3       text6

it feels like a sys_connect_by_path or windowed lag - but I'm not seeing it...

Answer 1

You didn't provide test data, so I will illustrate on the scott.emp table instead.

select  level,
        substr(sys_connect_by_path(rn, '.'), 2) as outline,
        empno
from    (
          select empno, mgr,
                 row_number() over (partition by mgr order by empno) as rn
          from   scott.emp
        )
start   with mgr is null
connect by mgr = prior empno
order   siblings by empno
;

LEVEL OUTLINE        EMPNO
----- -------------- -----
    1 1               7839
    2 1.1             7566
    3 1.1.1           7788
    4 1.1.1.1         7876
    3 1.1.2           7902
    4 1.1.2.1         7369
    2 1.2             7698
    3 1.2.1           7499
    3 1.2.2           7521
    3 1.2.3           7654
    3 1.2.4           7844
    3 1.2.5           7900
    2 1.3             7782
    3 1.3.1           7934

In the subquery, we give a sequential number to "siblings" (rows/employees that have the same direct parent), and we use that in sys_connect_by_path. To get the "right" ordering from the hierarchical query, you need to order siblings the same way you ordered them in the subquery (in my case, by empno, which is primary key; in your case, if col1 may have duplicates, order by col1, rowid in both places to break ties).