gistfile1.sql

WITH DeptList AS
	(select rbc.RbcId as GroupId, CAST(NULL as BIGINT) as ParentId, d.DepartmentId, d.Description, CAST(g.Name as VARCHAR(MAX)) as Name, 1 as level
	from relationship r1
	INNER JOIN Rbc rbc ON r1.RightId = rbc.RbcId
	INNER JOIN Department d on r1.LeftId=d.DepartmentId
	INNER JOIN [Group] g on d.DepartmentId=g.GroupId
	where r1.RelationshipTypeId = 2
	UNION ALL
	select DL.GroupId, p.DepartmentId ParentId, c.DepartmentId, c.Description, CAST(DL.Name + '>' + g.Name as VARCHAR(MAX)) as Name, DL.level + 1
	from relationship r2
	INNER JOIN Department as p ON r2.RightId = p.DepartmentId
	INNER JOIN Department as c ON r2.LeftId=c.DepartmentId
	INNER JOIN DeptList as DL ON p.DepartmentId = DL.DepartmentId
	INNER JOIN [Group] g on c.DepartmentId=g.GroupId)
select * from DeptList;