Запрос на обход дерева.

Приветствую!
Есть таблица T с двумя полями ID и Parent_ID типа nvarchar, где Parent_ID ссылается на ID, ID уникален.
Нужно получить для каждой записи в T значение верхнего узла дерева, у которого Parent_ID is null.
На основе MSDN я соорудил вот такое:

WITH DirectReports(ID, Parent_ID, Sort) AS 
(
    SELECT ID, Parent_ID, ID
    FROM T
    WHERE Parent_ID IS NULL
    UNION ALL
    SELECT e.ID, e.Parent_ID, RTRIM(Sort) + '| ' + e.ID
    FROM T e
        INNER JOIN DirectReports d
        ON e.Parent_ID= d.ID
)
SELECT ID, 
CASE WHEN Parent_ID IS NOT NULL THEN SUBSTRING(Sort,1,CHARINDEX('|', Sort)-1) ELSE ID END
FROM DirectReports
ORDER BY Sort

но мне кажется, должны быть варианты и поизящнее.
Может кто сталкивался, задача-то типичная.

А это вот чего такое: '| '? Тоже в msdn было?

Гавриленко Сергей Алексеевич,

Ага:

F. Using a recursive common table expression to display a hierarchical list
The following example builds on Example C by adding the names of the manager and employees, and their respective titles. The hierarchy of managers and employees is additionally emphasized by indenting each level. 

 Copy Code 
USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

 

Oblom,

но спасибо за мысль:

WITH DirectReports(ID, Parent_ID, Sort) AS 
(
    SELECT ID, Parent_ID, ID
    FROM T
    WHERE Parent_ID IS NULL
    UNION ALL
    SELECT e.ID, e.Parent_ID, d.Sort
    FROM T e
        INNER JOIN DirectReports d
        ON e.Parent_ID= d.ID
)
SELECT ID, Sort
FROM DirectReports
ORDER BY Sort

Извините, что отнял время.

Oblom,

у Вас небольшая неточность, приводящая к неверному результату.
в строке 'SELECT e.ID, e.Parent_ID, d.sort ' должно быть
'SELECT e.ID, e.Parent_ID, d.id ' и в последнем select - distinct.

Как-то вот так :

WITH DirectReports(ID, Parent_ID, Sort) AS
(
SELECT ID, Parent_ID, ID
FROM #T
WHERE Parent_ID IS NULL
UNION ALL
SELECT e.ID, e.Parent_ID, d.id
FROM #T e
INNER JOIN DirectReports d
ON e.Parent_ID= d.ID
)
SELECT distinct ID, Sort
FROM DirectReports
ORDER BY Sort