WITH DepartmentHierarchy AS (
 
    SELECT 
        d.Id,
        dn.Name AS DepartmentName,
        d.IdParentDepartment,
        1 AS Level,
        CAST(dn.Name AS NVARCHAR(MAX)) AS FullDepartmentName,
        d.IdDepartmentName
    FROM dbo.Departments d
    INNER JOIN dbo.DepartmentName dn ON d.IdDepartmentName = dn.Id
    WHERE d.IdParentDepartment IS NULL -- Корневые департаменты
 
    UNION ALL
 
    SELECT 
        d.Id,
        dn.Name AS DepartmentName,
        d.IdParentDepartment,
        dh.Level + 1 AS Level,
        CAST(dh.FullDepartmentName + ' > ' + dn.Name AS NVARCHAR(MAX)) AS FullDepartmentName,
        d.IdDepartmentName
    FROM dbo.Departments d
    INNER JOIN dbo.DepartmentName dn ON d.IdDepartmentName = dn.Id
    INNER JOIN DepartmentHierarchy dh ON d.IdParentDepartment = dh.Id
)
 
SELECT 
    dh.FullDepartmentName AS DepartmentHierarchy,
    dh.Level
FROM DepartmentHierarchy dh
ORDER BY dh.Level, dh.FullDepartmentName;

SELECT 
    d.Id AS DepartmentId,
    d1.Name AS Level1,        -- Первый уровень (корневой отдел)
    d2.Name AS Level2,        -- Второй уровень (подразделение корневого отдела)
    d3.Name AS Level3         -- Третий уровень (дочернее подразделение)
FROM Departments d
-- Первый уровень: текущий отдел
INNER JOIN DepartmentName d1 ON d1.Id = d.IdDepartmentName
-- Второй уровень: родитель текущего отдела
LEFT JOIN Departments dp ON dp.Id = d.IdParentDepartment
LEFT JOIN DepartmentName d2 ON d2.Id = dp.IdDepartmentName
-- Третий уровень: родитель второго уровня
LEFT JOIN Departments gdp ON gdp.Id = dp.IdParentDepartment
LEFT JOIN DepartmentName d3 ON d3.Id = gdp.IdDepartmentName
ORDER BY Level1, Level2, Level3;

CREATE TABLE DepartmentName
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name nvarchar(180) NOT NULL
);
 
CREATE TABLE Departments
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    IdDepartmentName INT NOT NULL,
    IdParentDepartment INT,
    FOREIGN KEY (IdDepartmentName) REFERENCES DepartmentName(Id),
    FOREIGN KEY (IdParentDepartment) REFERENCES DepartmentName(Id)
);