Условие: имеется таблица, которая хранит в себе иерархию, например, подразделений.
Задача: заполнить в таблице полное наименование подразделения (от родителей к текущей записи).
Для решения поставленной задачи, нам необходимо построить дерево подразделений от самого потомка к родителю и развернуть наименования от родителя к потомку. Поиск в сети выдает много результатов, но нам важно, чтобы наименования были развернуты от родителя к потомку. Основное решение я нашел тут: SQL queries to manage hierarchical or parent child. В той статье приводится несколько вариантов работы с иерархическими данными. Под нашу задачу подходит вариант: All Possible Parents In A Column.
Немного трансформировав его, получаем:
with FullPathDepartment(id, parent_id, name, parents, branchname, branch_id) as
(
select FirtGeneration.department_id, FirtGeneration.parent_id, iif(FirtGeneration.name is null, FirtGeneration.short_name, FirtGeneration.name), CAST(iif(FirtGeneration.name is null, FirtGeneration.short_name, FirtGeneration.name) AS VARCHAR(MAX)), cast (tops.name as nvarchar(max)), tops.id
from departments as FirtGeneration, @topId as tops
where FirtGeneration.parent_id = tops.id
union all
select
NextGeneration.department_id, Parents.id, iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name),
CAST(CASE WHEN Parents.parents = ''
THEN(CAST(iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name) AS VARCHAR(MAX)))
ELSE(Parents.parents + ', ' + CAST(iif(NextGeneration.name is null, NextGeneration.short_name, NextGeneration.name) AS VARCHAR(MAX)))
END AS VARCHAR(MAX)),
Parents.branchname,
Parents.branch_id
from departments as NextGeneration
inner join FullPathDepartment as Parents on NextGeneration.parent_id = Parents.id
)
update d1
set d1.full_name = d2.parents
from departments as d1
INNER JOIN FullPathDepartment as d2 on d1.department_id = d2.id
OPTION(MAXRECURSION 32767);
Таблица department:
CREATE TABLE departments (
[department_id] int not null identity,
[parent_id] int ,
[name] varchar(255) not null,
[short_name] varchar(255) not null,
PRIMARY KEY( [department_id] ),
CONSTRAINT FK_parent FOREIGN KEY (parent_id)
REFERENCES department (department_id)
);
В самом начале запросы мы выбираем верхние подразделения, те, которые имеют только самого верхнего родителя. Это и будет первое поколение.
insert into @topId select dpt.department_id, iif(dpt.name is null, dpt.short_name, dpt.name)
from departments as dpt inner join departments as parent on dpt.parent_id = parent.department_id
where parent.parent_id is null;
И дальше объединяем со всеми потомками.