Generating a Tree Path with a CTE

create table departments(id int , department varchar(200), parent int)

insert into departments(id , department , parent )

select 1, 'Camping', 0 UNION ALL
select 2, 'Cycle', 0 UNION ALL
select 3, 'Snowsports', 0 UNION ALL
select 4, 'Fitness', 0 UNION ALL
select 5, 'Tents',1 UNION ALL
select 6, 'Backpacks',1 UNION ALL
select 7, 'Sleeping Bags',1 UNION ALL
select 8, 'Cooking',1 UNION ALL
select 18, '1 Person',5 UNION ALL
select 19, '2 Person',5 UNION ALL
select 20, '3 Person',5 UNION ALL
select 21, '4 Person',5 UNION ALL
select 22, 'Family Camping',19 UNION ALL
select 23, 'Backpacking',19 UNION ALL
select 24, 'Mountaineering',19



WITH departmentcte(deptid, department, parent, LEVEL, treepath) AS

( SELECT id AS deptid, department, parent, 0 AS LEVEL,

CAST(department AS VARCHAR(1024)) AS treepath

FROM departments

WHERE parent = 0

UNION ALL

SELECT d.id AS deptid, d.department, d.parent,

departmentcte.LEVEL + 1 AS LEVEL,

CAST(departmentcte.treepath + ' -> ' +

CAST(d.department AS VARCHAR(1024))

AS VARCHAR(1024)) AS treepath

FROM departments d

INNER JOIN departmentcte

ON departmentcte.deptid = d.parent)

SELECT *

FROM departmentcte

ORDER BY treepath;