Поиск старшего предка иерархии

[Lizooonchik]

Добрый день!

Подскажите, как найти старшего предка в иерархии. Например, в таблице ниже указаны ID и ID_Родителя для каждой строки, причем ID_Родителя не самого старшего, а следующего по иерархиии. Нужно вывести столбец с наименованием старшего родителя, то есть Департамента.

CREATE TABLE Structure(
ID INT,
ID_Родителя INT,
Name VARCHAR(25)
);

INSERT INTO Structure (ID, ID_Родителя, Name) VALUES
(1, 0, 'Департамент1'),
(2, 0, 'Департамент2'),
(3, 0, 'Департамент3'),
(4, 1, 'Отдел11'),
(5, 1, 'Отдел12'),
(6, 2, 'Отдел21'),
(7, 2, 'Отдел22'),
(8, 6, 'Группа211'),
(9, 6, 'Группа212'),
(10, 3, 'Отдел31'),
(11, 3, 'Отдел32'),
(12, 3, 'Отдел33');

Должен получиться такой результат (см. вложение)

В Power BI для получения такого результата я использовала функции PATH, PATHITEM.
В SQL пробовала соединение таблицы самой с собой, но безрезультатно.

[Valick]

Сообщение от Lizooonchik

В SQL пробовала соединение таблицы самой с собой, но безрезультатно.

Не стесняйтесь показать запрос. А в целом при таком методе хранения дерева в БД либо рекурсия (что проще), либо количество джоинов равное уровню вложенности (уровень вложенности необходимо знать заранее)

[Lizooonchik]

Мой запрос:

SELECT table1.ID AS ID1, table1.ID_Родителя AS ID_Родителя1, table1.Name AS Name1,
table2.ID AS ID2, table2.ID_Родителя AS ID_Родителя2, table2.Name AS Name2,
table3.ID AS ID3, table3.ID_Родителя AS ID_Родителя3, table3.Name AS Name3
FROM Structure AS table1
LEFT JOIN Structure AS table2 ON table2.ID=table1.ID_Родителя
LEFT JOIN Structure AS table3 ON table3.ID=table2.ID_Родителя

В результате получаем вот такую таблицу (см. вложение), а требуется один столбец со старшим предком (Департаментом).

[olgavv]

Чтоб каждый раз, когда понадобится предок, не изобретать велосипед, то предлагаю один раз создать функцию:

CREATE FUNCTION dbo.СтаршийПредок (@id_ int)
Returns int
AS
BEGIN
declare @id_rod int
select @id_ = ID, @id_rod = ID_Родителя From Structure where ID = @id_
while (@id_rod > 0) begin
select @id_ = ID, @id_rod = ID_Родителя From Structure where ID = @id_rod
end
Return (@id_)
END

А потом можете ее везде использовать:

Sselect *, dbo.СтаршийПредок(ID) From Structure

ну можно функцию переписать, чтоб не идентификатор старшего предка возвращала, а сразу имя.
А вообще по хорошему, если предок верхнего уровня нужен довольно часто, - добавьте в Structure еще одно поле: ID_СтаршегоРодителя, которое можно инициализировать описанной выше функцией, и избавитесь от головной боли.