get parent child incremental paths in one group-CodePudding

I'm looking for a way where could get parent child paths in incremental form. Here is my input data

child  Parent   Society   Relation
B       A       YUM       ppp
C       B       YUM       qqq
D       C       YUM       ccc
A       None    YUM       ooo
A       None    PUM       ooo
Y       Z       YUM       qwe
W       Y       YUM       dfr
V       W       YUM       asd
Z       None    YUM       rtg

this is expected

childId   PATH                  level
A         ooo                    1
B         ooo|ppp                2
C         ooo|ppp|qqq            3
D         ooo|ppp|qqq|ccc        4
A         ooo                    1
Z         rtg                    1
Y         rtg|qwe                2
W         rtg|qwe|dfr            3
V         rtg|qwe|dfr|asd        4

As you can see, number of records are same, but getting respective incremental paths using relations and parent/child combination.

I'm not able to think any way, please share suggestions if possible

Note: would like to avoid functions like sys_connect_by_path, because I want to use this query on different platforms where sql with less functions are allowed

Update on astentx 's answer

this part will give 3 records

select
    society, child, relation, 1 as lvl
  from input
  where parent is null

output (hier)

SOCIETY CHILD   RELATION    LVL
YUM      A       ooo        1
PUM      A       ooo        1
YUM      Z       rtg        1

so when hier union all with recursive part, it will give below records after first iteration only

YUM A ooo       1    > from hier
YUM B ooo|ppp   2    >  came from inner join
and so on..this will become updated hier to join with input

so when inner join will happen between above updated hier with input, then <> from hier> from above output wont take part again in inner join ?

CodePudding user response：

Something like this, perhaps?

Sample data:

SQL> with test (child, parent, society, relation) as
  2    (select 'B', 'A' , 'YUM', 'ppp' from dual union all
  3     select 'C', 'B' , 'YUM', 'qqq' from dual union all
  4     select 'D', 'C' , 'YUM', 'ccc' from dual union all
  5     select 'A', null, 'YUM', 'ooo' from dual union all
  6     select 'A', null, 'PUM', 'ooo' from dual union all
  7     select 'Y', 'Z' , 'YUM', 'qwe' from dual union all
  8     select 'W', 'Y' , 'YUM', 'dfr' from dual union all
  9     select 'V', 'W' , 'YUM', 'asd' from dual union all
 10     select 'Z', null, 'YUM', 'rtg' from dual
 11    ),

Query begins here:

 12  temp as
 13    (select society,
 14       child,
 15       ltrim(sys_connect_by_path (relation, ', '), ', ') path,
 16       level lvl
 17     from test
 18     start with parent is null
 19     connect by prior child = parent
 20    )
 21  select society, child, path, lvl
 22  from temp
 23  group by society, child, path, lvl
 24  order by society, lvl;

SOCIETY    CHILD PATH                        LVL
---------- ----- -------------------- ----------
PUM        A     ooo                           1
YUM        Z     rtg                           1
YUM        A     ooo                           1
YUM        Y     rtg, qwe                      2
YUM        B     ooo, ppp                      2
YUM        C     ooo, ppp, qqq                 3
YUM        W     rtg, qwe, dfr                 3
YUM        D     ooo, ppp, qqq, ccc            4
YUM        V     rtg, qwe, dfr, asd            4

9 rows selected.

SQL>

CodePudding user response：

You may use recursive CTE to build a hierarchy in Oracle as well as in other DBMS that support it.

create table input (child, parent, society, relation) as 
  select 'B', 'A' , 'YUM', 'ppp' from dual union all
  select 'C', 'B' , 'YUM', 'qqq' from dual union all
  select 'D', 'C' , 'YUM', 'ccc' from dual union all
  select 'A', null, 'YUM', 'ooo' from dual union all
  select 'A', null, 'PUM', 'ooo' from dual union all
  select 'B', 'A' , 'PUM', 'ppp' from dual union all
  select 'Y', 'Z' , 'YUM', 'qwe' from dual union all
  select 'W', 'Y' , 'YUM', 'dfr' from dual union all
  select 'V', 'W' , 'YUM', 'asd' from dual union all
  select 'Z', null, 'YUM', 'rtg' from dual

with hier(society, child, path, lvl) as (
  /*Starting point*/
  select
    society, child, relation, 1 as lvl
  from input
  where parent is null
  
  union all
  
  /*Recursion step*/
  select
    hier.society,
    input.child,
    hier.path || ' -> ' || input.relation,
    hier.lvl   1
  from hier
    /*Inner join will reduce the number of rows added at each step*/
    join input
    on hier.society = input.society
    and hier.child = input.parent
)
select *
from hier
order by society, path asc

SOCIETY	CHILD	PATH	LVL
PUM	A	ooo	1
PUM	B	ooo -> ppp	2
YUM	A	ooo	1
YUM	B	ooo -> ppp	2
YUM	C	ooo -> ppp -> qqq	3
YUM	D	ooo -> ppp -> qqq -> ccc	4
YUM	Z	rtg	1
YUM	Y	rtg -> qwe	2
YUM	W	rtg -> qwe -> dfr	3
YUM	V	rtg -> qwe -> dfr -> asd	4

db<>fiddle here