CTE Tree Path WBS

cte_of_wbsproject.sql

WITH wbsproject(id, NAME, parentid, level, treepath) AS (
          SELECT id,
                NAME,
                parentid,
                0 AS LEVEL,
                Cast(NAME AS VARCHAR(1024)) AS treepath
         FROM   port_project
         WHERE  parentid = 0
         UNION ALL -- and now for the recursive part 
         SELECT d.id,
                d.NAME,
                d.parentid,
                wbsproject.level + 1 AS LEVEL,
                Cast(wbsproject.treepath + ' / ' + Cast(d.NAME AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath
         FROM   port_project d
                INNER JOIN wbsproject
                        ON wbsproject.id = d.parentid
)

SELECT * FROM   wbsproject ORDER  BY treepath;