Hi everyone,
Could anyone help me with this query? And tell me if we can do that in SQL? I have a messy structure like this one. Where groups can be parts of other groups. We can have cycle and orphan groups etc.
Each group can have many childs. I have provided a sample data here:
WITH groups AS
(
SELECT '10' AS group_id, 'root' AS group_name, 'dev' as child FROM dual UNION ALL
SELECT '99' AS group_id, 'dev' AS group_name, 'fold1' as child FROM dual UNION ALL
SELECT '76' AS group_id, 'fold2' AS group_name, 'dev' as child FROM dual UNION ALL
SELECT '34' AS group_id, 'fold3' AS group_name, 'dev' as child FROM dual UNION ALL
SELECT '99' AS group_id, 'dev' AS group_name, 'root' as child FROM dual UNION ALL
SELECT '10' AS group_id, 'root' AS group_name, 'etc' as child FROM dual UNION ALL
SELECT '98' AS group_id, 'etc' AS group_name, 'var' as child FROM dual UNION ALL
SELECT '100' AS group_id, 'var' AS group_name, 'foo' as child FROM dual
)
SELECT *
FROM groups;
And I was wondering if we could get a result like this:
id name fullchilds: has cycle
10 root root > dev > fold1 no
10 root root > dev > root yes
10 root root > etc > var > foo no
99 dev dev > fold1 no
76 fold3 fold3 > dev > fold1 no
99 dev dev > root > dev yes
98 etc etc > var > foo no
100 var var > foo no
For each group, I would like to get a concatenation of all descending group names. And, if there is a cycle, to have a column to let me know there is a cycle. Is this feasible?
Regards,
PS: I'm on oracle 12.2