select
    cl.classstructureid,
    level as pseudo_level, --the CLASSANCESTOR table already has an OOB HIERARCHYLEVEL column. But the level numbers in that column are in reverse-order (sorted descending) and the level numbers start at 0 instead of 1 -- which isn't what we want.
    count(level) over (partition by connect_by_root(cl.classificationid)) as level_count,
    cl.classificationid,
    cl.description,
    cl.cghierarchypath,
    --ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath, --the hierarchy path can be calculated dynamically too.
    cl.parent,
    cl.haschildren,
    connect_by_root(cl.classstructureid) as top_classstructureid,
    connect_by_root(cl.classificationid) as top_classificationid,
    uw.usewith,
    cl.cgactive
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 cl.parent is null
connect by prior cl.classstructureid = parent
order by
    top_classstructureid,  --handles scenarios like the FACILITIES classifications -- we have top-level FACILITIES classifications for both ASSET and WO (different sets of classifications)
    cghierarchypath

Notes:

• Related: Maximo classification summary query
• LEVEL_COUNT is the number of classifications that are in the same set (have the same top CLASSSTRUCTUREID).
• Hierarchy Path:
  • In my case, CGHIERARCHYPATH is a custom persistent field in CLASSSTRUCTURE, populated by an automation script: mbo.setValue('CGHIERARCHYPATH', mbo.getString('HIERARCHYPATH'))
  • But it's easy to calculate the hierarchy path dynamically in the query above too, by adding this calculated column to the SELECT statement: ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath,
  • More info here: Generate hierarchical path