Generate hierarchical path

Question

I have a classstructure table:

create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));

insert into classstructure(classstructureid, classificationid, parent) values(1001, 'FLEET', null);
insert into classstructure(classstructureid, classificationid, parent) values(1002, 'LIGHTDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1004, 'MEDIUMDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1022, 'ACTIVETRANSPORTATION', null);
insert into classstructure(classstructureid, classificationid, parent) values(1023, 'FACILITYWALKWAY', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1024, 'TRAIL', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1085, 'SIDEWALK', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1091, 'SDWRAMP', 1085);
commit;

select * from classstructure;

CLASSSTRUCTUREID CLASSIFICATIONID              PARENT
---------------- ------------------------- ----------
            1001 FLEET                               
            1002 LIGHTDUTYVEHICLE                1001
            1004 MEDIUMDUTYVEHICLE               1001

            1022 ACTIVETRANSPORTATION                
            1023 FACILITYWALKWAY                 1022
            1024 TRAIL                           1022
            1085 SIDEWALK                        1022
            1091 SDWRAMP                         1085

I would like to collapse the records down to hierarchy paths:

HIERARCHYPATH
---------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE 

ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

How can I do this?

I realized after I wrote this question that I should have included rows that are the first level of the hierarchy. [More info in my answer below](https://stackoverflow.com/a/67903948/10936066). I'll leave the question as-is...I just wanted to point that out for any Maximo users who might have found my original question and this answer a confusing. (It was my mistake, not Barbaros Özhan's mistake.) — User1974, Oct 13 '21 at 23:32

score 2 · Accepted Answer · answered Sep 21 '19 at 13:07

2

You can use sys_connect_by_path()( since Oracle 10g Release 2 ) function :

select ltrim(sys_connect_by_path(classificationid, ' \ '),' \ ') as hierarchypath
  from classstructure c
 where parent is not null
 start with parent is null
connect by prior classstructureid = parent;

HIERARCHYPATH
--------------------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE
ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

answered Sep 21 '19 at 13:07

Barbaros Özhan

59,113
10
31
55

I realized after I wrote this question that I should have included rows that are the first level of the hierarchy. [More info in my answer below](https://stackoverflow.com/a/67903948/10936066). I'll leave the question as-is...I just wanted to point that out for any Maximo users who might have found my original question and this answer a confusing. (It was my mistake, not Barbaros Özhan's mistake.) – User1974 Oct 13 '21 at 23:31

User1974 · Answer 2 · 2021-10-13T23:33:52.503

Edit 1:

I realized after I wrote this question that I should have included rows that are the first level of the hierarchy.

So here's an updated version of @Barbaros' query. I removed the WHERE clause.

select ltrim(sys_connect_by_path(classificationid, ' \ '),' \ ') as hierarchypath
from maximo.classstructure
start with parent is null
connect by prior classstructureid = parent;

Edit 2:

For bonus points, here's a version of the query that also includes Maximo's USE WITH objects (grouped by & concatenated):

select 
    ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath,
    cl.classstructureid,
    cl.description,
    uw.usewith
from 
    maximo.classstructure cl
left join
    (
    select 
        classstructureid, 
        listagg(objectname,', ') within group(order by objectname) as usewith
    from 
        maximo.classusewith
    group by 
        classstructureid
    ) uw
    on cl.classstructureid = uw.classstructureid
start with parent is null
connect by prior cl.classstructureid = parent
order by
    hierarchypath

Edit 3:

On a side note, here's how to group-by-concat USEWITH in a subquery (for scenarios where we can't use the JOIN clause, only the WHERE clause).

Of course, the subquery performance is a lot worse than a proper join. We'd want to use a join instead of a subquery whenever possible.

select 
    cl.classstructureid,
    cl.description,
   
    (select 
        listagg(objectname,', ') within group(order by objectname) as usewith
    from 
        maximo.classusewith
    group by 
        classstructureid
    having 
        classstructureid = cl.classstructureid
    ) as usewith

from 
    maximo.classstructure cl
order by
    description

User1974 · Answer 3 · 2021-10-15T01:26:31.987

0

The same sort of query can be applied to Maximo asset hiearchy too (not just classifications):

select 
    assetnum,
    ltrim(sys_connect_by_path(assetnum, ' \ '),' \ ') as path,
    level
from 
    maximo.asset
start with parent is null
connect by prior assetnum = parent

I also added a LEVEL column that gives us the level number.

edited Oct 15 '21 at 01:26

answered Oct 13 '21 at 23:20

User1974

276
1
17
63

Generate hierarchical path

3 Answers3