By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
UID INTEGER NOT NULL
,Name VARCHAR(7) NOT NULL
,Generation INTEGER NOT NULL
,Parent_UID INTEGER NOT NULL
);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (1,'Parent1',1,0);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (3,'Parent2',1,0);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (7,'Parent4',1,0);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (14,'Child7',2,3);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (17,'Child8',2,3);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (20,'Parent8',1,0);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (55,'Child9',2,7);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (75,'Child12',3,55);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (90,'Child40',3,17);
INSERT INTO mytable(UID,Name,Generation,Parent_UID) VALUES (95,'Child20',2,7);
with recursive cte(uid, name, generation, parent_uid, path) as (
select t.*, cast(lpad(uid, 3, 0) as char(100)) from mytable t where parent_uid = 0
union all
select t.*, concat(c.path, '/', lpad(t.uid, 3, 0))
from cte c
inner join mytable t on t.parent_uid = c.uid
)
select * from cte order by path
| uid | name | generation | parent_uid | path |
|---|---|---|---|---|
| 1 | Parent1 | 1 | 0 | 001 |
| 3 | Parent2 | 1 | 0 | 003 |
| 14 | Child7 | 2 | 3 | 003/014 |
| 17 | Child8 | 2 | 3 | 003/017 |
| 90 | Child40 | 3 | 17 | 003/017/090 |
| 7 | Parent4 | 1 | 0 | 007 |
| 55 | Child9 | 2 | 7 | 007/055 |
| 75 | Child12 | 3 | 55 | 007/055/075 |
| 95 | Child20 | 2 | 7 | 007/095 |
| 20 | Parent8 | 1 | 0 | 020 |