CONNECT BY PRIOR and concatenation of parent – child hierarchy

I have this table:

img

Desired result:

img

What i’m trying:

select SEG from my_table CONNECT BY PRIOR PARENT_SEG_ID = SEG_ID;

So, in the column LEVEL_LOCATOR we can see f.e. that the LEVEL 5.9 has 6 childs, and 5.9.4 has 2 childs. And, f.e., the parent of the child 5.9.1 is Postpaid that has the SEG_ID = 361978 and it’s in the row of this child as PARENT_SEG_ID. So parent and child connect with SEG_ID and PARENT_SEG_ID.

I’m trying to do this but so far i can’t have the result i want.

Note: The col_a is the concatenation SEG column in the following format: parent - child - child (if it has childs)

Answer

I think you need to walk the tree the other way around, and can then use sys_connect_by_path() to concatenate the segment names, and connect_by_isleaf to only show the leaf nodes:

select col_a, col_b
from (
  select ltrim(sys_connect_by_path(seg, ' - '), ' - ') as col_a,
    seg_id as col_b,
    connect_by_isleaf as isleaf
  from my_table
  start with seg = 'Prepaid'
  connect by parent_seg_id = prior seg_id
)
where isleaf = 1;

COL_A                                                   COL_B
-------------------------------------------------- ----------
Prepaid                                                361977
Prepaid - Voice Mobile                             2687522995
Prepaid - Internet Mobile                          2687525564
Prepaid - Data Mobile                              2687527808
Prepaid - Voice Fixa - Default                     2687531979
Prepaid - Voice Fixa - Internal PABX               2687534794

The ltrim() is there to remove an extra - that the path includes at the start.

But you need a starting condition; I’ve gone with ‘Prepaid’, which includes an extra row in the output, but you could start with seg_id = 361978 instead, or some other condition depending on what data you actually want to see.

You could also using consider recursive subquery factoring.