cte_of_wbsproject_with_indentation.sql

-- Using the REPLICATE function to indent with a '.  ' for each level
WITH WBSProject(Id, name, Parentid, Level, TreePath) AS (
  	SELECT
		id as 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 as 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 Id, Level, REPLICATE('  .  ', Level) + name as Node
FROM WBSProject
ORDER BY TreePath;