It is quite hard to match that exact output structure, plus I'm not sure if you were just lazy when making the example or I am understanding it wrong, but the tree data does not evaluate to your example. I will accept your statement of "...to be kind of:" to mean that you were not being accurate. That said, here is a script that achieves it (final query below) if my understanding of your request is correct. If it isn't, please say how exactly and give an example that displays accurate logic. Since you indicated wanting to learn and not simply have a result - I have included several interim queries (including some unnecessary fields) to show what each step of the final query's CTE achieves. Please ask if anything is unclear, and good luck! ``` -- SQLite version 3.35.4 [ Release: 2021-04-02 ] on SQLitespeed version 2.1.3.11. -- ================================================================================ CREATE TABLE "PagesTreeTT1" ( "ID_Page" INTEGER, "TxtID_Page" TEXT(25) NOT NULL, "ParentPage_Id" INTEGER, "PgSlug" TEXT(120) NOT NULL, "PgDescr" TEXT(200), "PgSort" integer(2) DEFAULT 50, PRIMARY KEY ("ID_Page") ); INSERT INTO PagesTreeTT1(ID_Page, TxtID_Page, ParentPage_Id, PgSlug, PgDescr, PgSort) VALUES ( 2, 'HistRchch ' , NULL , 'h' , 'Histoire d''une recherche' , 10 ) ,( 3, 'TrvLieu ' , NULL , 't' , 'Trouver un lieu' , 10 ) ,( 4, 'GDN_ ' , 2 , 'g' , 'Gedinne - et rien d''autre' , 20 ) ,( 5, 'gTrv ' , 2 , 'j' , 'J''ai trouvé' , 30 ) ,( 6, 'dots ' , 2 , '.' , '...' , 40 ) ,( 7, 'decouvert ' , 2 , 'd' , 'Découverte' , 20 ) ,( 8, 'testAccent1' , NULL , 'a' , 'aaa éàçè' , 10 ) ,( 9, 'testAccent2' , 7 , 'b' , 'bbb &$^~' , 20 ) ,( 10, 'SsLaForge ' , 7 , 's' , 'Sous la Forge - Copinette-' , 30 ) ,( 11, 'inaug ' , 7 , 'i' , 'Inauguration' , 40 ) ,( 12, 'chantier_ ' , NULL , 'u' , 'Un chantier?' , 50 ) ,( 13, 'autour ' , NULL , 'e' , 'Et autour de ce site' , 60 ) ,( 14, 'infos ' , NULL , 'ii' , 'Infos utiles' , 70 ) ,( 15, 'WillVlg ' , 14 , 'w' , 'Willerzie (village)' , 10 ) ,( 16, 'GdnCmn ' , 14 , 'gg' , 'Gedinne (Commune)' , 20 ) ,( 17, 'commerces ' , 14 , 'c' , 'Commerces' , 30 ) ,( 18, 'services ' , 14 , 's' , 'Services' , 40 ) ,( 19, 'VieLocale- ' , 14 , 'v' , 'Vie locale et régionale' , 50 ) ,( 20, 'SocioEco ' , 19 , 'ss' , 'Socio-économie' , 10 ) ,( 21, 'culturel ' , 19 , 'cc' , 'Culturel' , 20 ) ,( 22, 'politik ' , 19 , 'p' , 'Politique' , 30 ) ,( 23, 'patrimn ' , 19 , 'pp' , 'Patrimoine' , 40 ) ,( 24, 'nature ' , 19 , 'n' , 'Nature' , 50 ) ,( 25, 'about ' , NULL , 'a' , 'A propos' , 95 ) ,( 26, 'hebergt ' , 18 , 'hh' , 'Hébergements' , 10 ) ,( 27, 'restaur ' , 18 , 'r' , 'Restauration' , 20 ) ,( 28, 'SportAct ' , 18 , 'sa' , 'Sports - Activités' , 30 ) ,( 29, 'brol ' , 18 , 'bb' , 'brol' , 35 ) ,( 30, 'bril ' , 18 , 'bbb', 'bril' , 36 ) SELECT * FROM PagesTreeTT1 ; -- | |ParentPa-| | | -- ID_Page |TxtID_Page |ge_Id |PgSlug|PgDescr |PgSort -- ------------|-------------|---------|------|----------------------------|------ -- 2 |HistRchch |NULL | h |Histoire d'une recherche | 10 -- 3 |TrvLieu |NULL | t |Trouver un lieu | 10 -- 4 |GDN_ |2 | g |Gedinne - et rien d'autre | 20 -- 5 |gTrv |2 | j |J'ai trouvé | 30 -- 6 |dots |2 | . |... | 40 -- 7 |decouvert |2 | d |Découverte | 20 -- 8 |testAccent1 |NULL | a |aaa éàçè | 10 -- 9 |testAccent2 |7 | b |bbb &$^~ | 20 -- 10 |SsLaForge |7 | s |Sous la Forge - Copinette- | 30 -- 11 |inaug |7 | i |Inauguration | 40 -- 12 |chantier_ |NULL | u |Un chantier? | 50 -- 13 |autour |NULL | e |Et autour de ce site | 60 -- 14 |infos |NULL | ii |Infos utiles | 70 -- 15 |WillVlg |14 | w |Willerzie (village) | 10 -- 16 |GdnCmn |14 | gg |Gedinne (Commune) | 20 -- 17 |commerces |14 | c |Commerces | 30 -- 18 |services |14 | s |Services | 40 -- 19 |VieLocale- |14 | v |Vie locale et régionale | 50 -- 20 |SocioEco |19 | ss |Socio-économie | 10 -- 21 |culturel |19 | cc |Culturel | 20 -- 22 |politik |19 | p |Politique | 30 -- 23 |patrimn |19 | pp |Patrimoine | 40 -- 24 |nature |19 | n |Nature | 50 -- 25 |about |NULL | a |A propos | 95 -- 26 |hebergt |18 | hh |Hébergements | 10 -- 27 |restaur |18 | r |Restauration | 20 -- 28 |SportAct |18 | sa |Sports - Activités | 30 -- 29 |brol |18 | bb |brol | 35 -- 30 |bril |18 | bbb |bril | 36 WITH CT(id, pid, lvl, srt, pgName) AS ( SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page FROM PagesTreeTT1 WHERE ParentPage_Id IS NULL UNION ALL SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page FROM CT JOIN PagesTreeTT1 AS P WHERE P.ParentPage_Id = CT.id LIMIT 1000 ) SELECT * FROM CT -- id |pid |lvl| srt|pgName -- ----|------|---|----|------------- -- 2 |NULL | 0 | 10 |HistRchch -- 3 |NULL | 0 | 10 |TrvLieu -- 8 |NULL | 0 | 10 |testAccent1 -- 12 |NULL | 0 | 50 |chantier_ -- 13 |NULL | 0 | 60 |autour -- 14 |NULL | 0 | 70 |infos -- 25 |NULL | 0 | 95 |about -- 4 |2 | 1 | 20 |GDN_ -- 7 |2 | 1 | 20 |decouvert -- 6 |2 | 1 | 40 |dots -- 5 |2 | 1 | 30 |gTrv -- 16 |14 | 1 | 20 |GdnCmn -- 19 |14 | 1 | 50 |VieLocale- -- 15 |14 | 1 | 10 |WillVlg -- 17 |14 | 1 | 30 |commerces -- 18 |14 | 1 | 40 |services -- 10 |7 | 2 | 30 |SsLaForge -- 11 |7 | 2 | 40 |inaug -- 9 |7 | 2 | 20 |testAccent2 -- 20 |19 | 2 | 10 |SocioEco -- 21 |19 | 2 | 20 |culturel -- 24 |19 | 2 | 50 |nature -- 23 |19 | 2 | 40 |patrimn -- 22 |19 | 2 | 30 |politik -- 28 |18 | 2 | 30 |SportAct -- 30 |18 | 2 | 36 |bril -- 29 |18 | 2 | 35 |brol -- 26 |18 | 2 | 10 |hebergt -- 27 |18 | 2 | 20 |restaur WITH CT(id, pid, lvl, srt, pgName) AS ( SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page FROM PagesTreeTT1 WHERE ParentPage_Id IS NULL UNION ALL SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page FROM CT JOIN PagesTreeTT1 AS P WHERE P.ParentPage_Id = CT.id LIMIT 1000 ) SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName, null, null, null, null, null, null, null, null, null, null FROM CT AS CTLvl0 WHERE CTLvl0.lvl = 0 UNION ALL SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName, CTLvl1.id, CTLvl1.pid, CTLvl1.lvl, CTLvl1.srt, CTLvl1.pgName, null, null, null, null, null FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 UNION ALL SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName, CTLvl1.id, CTLvl1.pid, CTLvl1.lvl, CTLvl1.srt, CTLvl1.pgName, CTLvl2.id, CTLvl2.pid, CTLvl2.lvl, CTLvl2.srt, CTLvl2.pgName FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2 -- id |pid |lvl| srt|pgName |null |null |null |null |null |null |null |null |null |null -- ----|------|---|----|-------------|------|------|------|------|-------------|------|------|------|------|------------- -- 2 |NULL | 0 | 10 |HistRchch |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 3 |NULL | 0 | 10 |TrvLieu |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 8 |NULL | 0 | 10 |testAccent1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 12 |NULL | 0 | 50 |chantier_ |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 13 |NULL | 0 | 60 |autour |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 25 |NULL | 0 | 95 |about |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL -- 2 |NULL | 0 | 10 |HistRchch |4 |2 |1 |20 |GDN_ |NULL |NULL |NULL |NULL |NULL -- 2 |NULL | 0 | 10 |HistRchch |5 |2 |1 |30 |gTrv |NULL |NULL |NULL |NULL |NULL -- 2 |NULL | 0 | 10 |HistRchch |6 |2 |1 |40 |dots |NULL |NULL |NULL |NULL |NULL -- 2 |NULL | 0 | 10 |HistRchch |7 |2 |1 |20 |decouvert |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |15 |14 |1 |10 |WillVlg |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |16 |14 |1 |20 |GdnCmn |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |17 |14 |1 |30 |commerces |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |NULL |NULL |NULL |NULL |NULL -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |NULL |NULL |NULL |NULL |NULL -- 2 |NULL | 0 | 10 |HistRchch |7 |2 |1 |20 |decouvert |10 |7 |2 |30 |SsLaForge -- 2 |NULL | 0 | 10 |HistRchch |7 |2 |1 |20 |decouvert |11 |7 |2 |40 |inaug -- 2 |NULL | 0 | 10 |HistRchch |7 |2 |1 |20 |decouvert |9 |7 |2 |20 |testAccent2 -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |20 |19 |2 |10 |SocioEco -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |21 |19 |2 |20 |culturel -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |24 |19 |2 |50 |nature -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |23 |19 |2 |40 |patrimn -- 14 |NULL | 0 | 70 |infos |19 |14 |1 |50 |VieLocale- |22 |19 |2 |30 |politik -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |28 |18 |2 |30 |SportAct -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |30 |18 |2 |36 |bril -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |29 |18 |2 |35 |brol -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |26 |18 |2 |10 |hebergt -- 14 |NULL | 0 | 70 |infos |18 |14 |1 |40 |services |27 |18 |2 |20 |restaur WITH CT(id, pid, lvl, srt, pgName) AS ( SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page FROM PagesTreeTT1 WHERE ParentPage_Id IS NULL UNION ALL SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page FROM CT JOIN PagesTreeTT1 AS P WHERE P.ParentPage_Id = CT.id LIMIT 1000 ), PT(PgPostA, PgPostB, PgPostC) AS ( SELECT CTLvl0.srt, null, null FROM CT AS CTLvl0 WHERE CTLvl0.lvl = 0 UNION ALL SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, null FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 UNION ALL SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, CTLvl0.srt||'-'||CTLvl1.srt||'-'||CTLvl2.srt FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2 ) SELECT DISTINCT PgPostA,PgPostB,PgPostC FROM PT ORDER BY PgPostA,PgPostB,PgPostC -- PgPostA|PgPostB|PgPostC -- -------|-------|---------- -- 10 |NULL |NULL -- 10 |10-20 |NULL -- 10 |10-20 |10-20-20 -- 10 |10-20 |10-20-30 -- 10 |10-20 |10-20-40 -- 10 |10-30 |NULL -- 10 |10-40 |NULL -- 50 |NULL |NULL -- 60 |NULL |NULL -- 70 |NULL |NULL -- 70 |70-10 |NULL -- 70 |70-20 |NULL -- 70 |70-30 |NULL -- 70 |70-40 |NULL -- 70 |70-40 |70-40-10 -- 70 |70-40 |70-40-20 -- 70 |70-40 |70-40-30 -- 70 |70-40 |70-40-35 -- 70 |70-40 |70-40-36 -- 70 |70-50 |NULL -- 70 |70-50 |70-50-10 -- 70 |70-50 |70-50-20 -- 70 |70-50 |70-50-30 -- 70 |70-50 |70-50-40 -- 70 |70-50 |70-50-50 -- 95 |NULL |NULL WITH CT(id, pid, lvl, srt, pgName) AS ( SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page FROM PagesTreeTT1 WHERE ParentPage_Id IS NULL UNION ALL SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page FROM CT JOIN PagesTreeTT1 AS P WHERE P.ParentPage_Id = CT.id LIMIT 1000 ), PT(PgPostA, PgPostB, PgPostC) AS ( SELECT CTLvl0.srt, null, null FROM CT AS CTLvl0 WHERE CTLvl0.lvl = 0 UNION ALL SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, null FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 UNION ALL SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, CTLvl0.srt||'-'||CTLvl1.srt||'-'||CTLvl2.srt FROM CT AS CTLvl0 JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2 ), DT(PgPostA, PgPostB, PgPostC) AS ( SELECT DISTINCT CASE WHEN PgPostB IS NULL THEN IFNULL(PgPostA,'') ELSE '' END, CASE WHEN PgPostC IS NULL THEN IFNULL(PgPostB,'') ELSE '' END, IFNULL(PgPostC,'') FROM PT ORDER BY PgPostA,PgPostB,PgPostC ) SELECT PgPostA, PgPostB, PgPostC FROM DT ; -- PgPostA|PgPostB|PgPostC -- -------|-------|---------- -- 10 | | -- |10-20 | -- | |10-20-20 -- | |10-20-30 -- | |10-20-40 -- |10-30 | -- |10-40 | -- 50 | | -- 60 | | -- 70 | | -- |70-10 | -- |70-20 | -- |70-30 | -- |70-40 | -- | |70-40-10 -- | |70-40-20 -- | |70-40-30 -- | |70-40-35 -- | |70-40-36 -- |70-50 | -- | |70-50-10 -- | |70-50-20 -- | |70-50-30 -- | |70-50-40 -- | |70-50-50 -- 95 | | ================================================================================ ```