Consultar recursivamente una tabla con la estructura de árbol

recursive-cte.sql

/*
Original Data:
 id     idparent    jobNO
--------------------------------
  1         0         1
  2         1         2
  3         1         3
  4         0         4
  5         4         5
  6         5         6
*/

/*
Expected Result:
id     idparent    jobNO   ListJob
----------------------------------------
  1         0         1        1
  2         1         2        1/2
  3         1         3        1/3
  4         0         4        4
  5         4         5        4/5
  6         5         6        4/5/6
*/

/*
Stackoverflow question and answer: 
https://stackoverflow.com/questions/37679357/sql-select-parent-child-recursive-in-one-field/37680286#37680286
*/

CREATE TABLE recursivas (
        id int not null,
        idparent int not null,
        jobno int not null
);

INSERT INTO recursivas VALUES 
    (1,0,1),
    (2,1,2),
    (3,1,3),
    (4,0,4),
    (5,4,5),
    (6,5,6);

WITH RECURSIVE CTE AS (
        -- This is end of the recursion: Select items with no parent
        SELECT id, idparent, jobno, jobno::varchar AS ListJob
        FROM recursivas
        WHERE idParent = 0
    UNION ALL
        -- This is the recursive part: It joins to CTE
        SELECT t.id, t.idparent, t.jobno,  c.ListJob || '/' || t.jobno::varchar AS ListJob
        FROM recursivas t
        INNER JOIN CTE c ON t.idParent = c.id
)
SELECT * FROM CTE ORDER BY id;