SQLite Forum

Populating a tree from a flat table of [sub]categories
Login

Populating a tree from a flat table of [sub]categories

(1) By Pierre-Yves Delens (PyDelens) on 2021-07-19 10:49:18 [link] [source]

Hello,

please see below table definition and datas.
From this flat table I need to display (in HTML, out of scope here) a tree of Categories.
At each level (3 of them, not more) the sorting discriminant is PgSort
At top level, by design ParentPage_Id is NULL
At 2d level ParentPage_Id is NOT NULL, but parent of parent is NULL
At 3d level, the item has 2 ancestors

I had some trials, with calculated fields, but I'll have to nest queries, or Window Functions.
A long time ago, i implemented this successfully in Access/VBA, but I think I need some help.
(I didn't practise SQL for some years, I'm back)

Output to be kind of :
_____________________________
PgPost / PgPost / PgPost
10
  10-10
  10-20
       10-20-10
       10-20-25
  10-30
       10-30-10
       10-30-15
       10-30-35
20
  20-05
       20-05-10
       20-05-15
_______________________________
Can someone give me just a hint, nudge, good example of nested queries or window function...?

Thanks on forward.
Pierre-Yves Delens (.be)


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")
);

sqlite> SELECT * FROM PagesTreeTT1;
+---------+-------------+---------------+--------+----------------------------+--------+
| ID_Page | TxtID_Page  | ParentPage_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     |
+---------+-------------+---------------+--------+----------------------------+--------+

(2) By ddevienne on 2021-07-19 12:11:45 in reply to 1 [link] [source]

Start with https://www.sqlite.org/lang_with.html

But could be done with a 3-way join too, since you explicitly want to do 3 levels only.

(4) By Pierre-Yves Delens (PyDelens) on 2021-07-20 08:00:08 in reply to 2 [source]

What a welcome on this forum! :-)

Already big thanks to both of you.

Very very helpful, .. just need some time to digest this 
   (which is a key issue in my projects).

Yours,

(3) By Ryan Smith (cuz) on 2021-07-19 12:52:57 in reply to 1 [link] [source]

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  |       |          

================================================================================

(5) By Pierre-Yves Delens (PyDelens) on 2021-07-29 11:13:42 in reply to 3 [link] [source]

Hello, Dear,

I got some result with this, adapted from SQLite.org docs; a little simpler for a beginning.
But the result isn't yet correct (see 'sort order' under SQL code):

PageTreeTT1_TreeVw02 :

WITH RECURSIVE
  under_root(ID_Page,level, lbl,slug,OnOff,sort) AS (
    SELECT
	PagesTreeTT1.ID_Page, 0, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.OnOff,PagesTreeTT1.PgSort
FROM 	PagesTreeTT1
WHERE PagesTreeTT1.ParentPage_Id ISNULL  AND PagesTreeTT1.OnOff = 1
    UNION ALL
    SELECT PagesTreeTT1.ID_Page, under_root.level+1, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.OnOff,PagesTreeTT1.PgSort
      FROM PagesTreeTT1 JOIN under_root ON PagesTreeTT1.ParentPage_Id=under_root.ID_Page
			WHERE PagesTreeTT1.OnOff = 1
     ORDER BY 2 DESC
  )
SELECT
	(substr('..........',1,level*3) || lbl) AS BranchString, 
	slug, sort
FROM
	under_root;

+-------------------------------+------------+------+
| BranchString                  | slug       | sort |
+-------------------------------+------------+------+
| Histoire d'une recherche      | HistRchch  | 10   |
| ...Gedinne - et rien d'autre  | GDN_       | 20   |
| ...Trouver un lieu            | TrvLieu    | 10   |
| ...J'ai trouvé                | gTrv       | 30   |
| Découverte                    | decouvert  | 20   |
| ...Sous la Forge - Copinette- | SsLaForge  | 30   |
| ...Inauguration               | inaug      | 40   |
| Un chantier?                  | chantier_  | 50   |
| Et autour de ce site          | autour     | 60   |
| Infos utiles                  | infos      | 70   |
| ...Gedinne (Commune)          | GdnCmn     | 20   |
| ...Vie locale et régionale    | VieLocale- | 50   |
| ......Socio-économie          | SocioEco   | 10   |
| ......Culturel                | culturel   | 20   |
| ......Nature                  | nature     | 50   |
| ......Patrimoine              | patrimn    | 40   |
| ......Politique               | politik    | 30   |
| ...Willerzie (village)        | WillVlg    | 10   |
| ...Commerces                  | commerces  | 30   |
| ...Services                   | services   | 40   |
| ......Sports - Activités      | SportAct   | 30   |
| ......Hébergements            | hebergt    | 10   |
| ......Restauration            | restaur    | 20   |
| A propos                      | about      | 95   |
+-------------------------------+------------+------+

Almost ok but the sort order (example lines 2,3,4) are not following the sort value in this subgroup.

The second problem is (see PageTreeTT1_TreeVw02a below): 
when adding the ParentPage_Id field (alias : parid) in the query, the output BranchString field is buggy for the rows of 1st level (top parents level : "Histoire d'une recherche") ; The BranchString is NULL.


PageTreeTT1_TreeVw02a :

WITH RECURSIVE
  under_root(ID_Page,level, lbl,slug, sort, OnOff, parid) AS (
    SELECT
	PagesTreeTT1.ID_Page, 0, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.PgSort, PagesTreeTT1.OnOff, PagesTreeTT1.ParentPage_Id
FROM 	PagesTreeTT1
WHERE PagesTreeTT1.ParentPage_Id ISNULL  AND PagesTreeTT1.OnOff = 1
    UNION ALL
    SELECT PagesTreeTT1.ID_Page, under_root.level+1, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.PgSort, PagesTreeTT1.OnOff, PagesTreeTT1.ParentPage_Id
      FROM PagesTreeTT1 JOIN under_root ON PagesTreeTT1.ParentPage_Id=under_root.ID_Page
			WHERE PagesTreeTT1.OnOff = 1
     ORDER BY 2 DESC
  )
SELECT (substr('..........',1,level*3) || lbl || '  ' || ID_Page || ' \< ' || parid ) as BranchString, slug, sort FROM under_root;

+-----------------------------------------+------------+------+
| BranchString                            | slug       | sort |
+-----------------------------------------+------------+------+
| NULL                                    | HistRchch  | 10   |
| ...Gedinne - et rien d'autre  4 \< 2   | GDN_       | 20   |
| ...Trouver un lieu  3 \< 2              | TrvLieu    | 10   |
| ...J'ai trouvé  5 \< 2                  | gTrv       | 30   |
| NULL                                    | decouvert  | 20   |
| ...Sous la Forge - Copinette-  10 \< 7 | SsLaForge  | 30   |
| ...Inauguration  11 \< 7                | inaug      | 40   |
| NULL                                    | chantier_  | 50   |
| NULL                                    | autour     | 60   |
| NULL                                    | infos      | 70   |
| ...Gedinne (Commune)  16 \< 14         | GdnCmn     | 20   |
| ...Vie locale et régionale  19 \< 14   | VieLocale- | 50   |
| ......Socio-économie  20 \< 19         | SocioEco   | 10   |
| ......Culturel  21 \< 19                | culturel   | 20   |
| ......Nature  24 \< 19                  | nature     | 50   |
| ......Patrimoine  23 \< 19              | patrimn    | 40   |
| ......Politique  22 \< 19               | politik    | 30   |
| ...Willerzie (village)  15 \< 14       | WillVlg    | 10   |
| ...Commerces  17 \< 14                  | commerces  | 30   |
| ...Services  18 \< 14                   | services   | 40   |
| ......Sports - Activités  28 \< 18     | SportAct   | 30   |
| ......Hébergements  26 \< 18            | hebergt    | 10   |
| ......Restauration  27 \< 18            | restaur    | 20   |
| NULL                                    | about      | 95   |
+-----------------------------------------+------------+------+

This 2d problem could be solved  with this endinc SQL string :
... ...	) SELECT
	( substr( '..........', 1, level * 3 ) || lbl || '  ' || ID_Page ) AS BranchString,
	slug, 	sort , parid FROM under_root;

But I 'd like to understand why buggy in a calculated/concatenated field, and not as plain field ('parid').

QUESTION / DEMAND : 
is there a way to correct this SQL?
Or should I switch on Ryan's example (which is a bit too difficult for me for the while)?

Thanks on forward