Wie kommst du darauf? Natürlich geht das. Du musst halt dein Ergebnis einfach nach Pfad sortieren. Siehe das Beispiel zu dem Link von vorhin:
USE AdventureWorks2008R2 ;
GO
WITH DirectReports ( ManagerID, EmployeeID, Title, DeptID, LEVEL, Path )
AS (
-- Anchor member definition
SELECT e.ManagerID ,
e.EmployeeID ,
e.Title ,
edh.DepartmentID ,
0 ,
'//' + CAST(e.EmployeeID AS VARCHAR(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID
AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID ,
e.EmployeeID ,
e.Title ,
edh.DepartmentID ,
Level + 1 ,
Path + '/' + CAST(e.EmployeeID AS VARCHAR(MAX))
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID
AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID ,
EmployeeID ,
SPACE(LEVEL) + Title ,
DeptID ,
LEVEL ,
Path
FROM DirectReports
INNER JOIN HumanResources.Department AS dp ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
OR Level = 0
ORDER BY PATH ;
GO
