true3less

WITH RecursiveDepartment AS (
SELECT
ID,
DepartmentName,
ParentID,
CAST(DepartmentName AS NVARCHAR(MAX)) AS Tree,
1 AS Level,
CONVERT(varchar(255), RIGHT('0000' + CAST(ID AS varchar(4)), 4)) AS Path
FROM
tblDepartment
WHERE
ParentID IS NULL
UNION ALL
SELECT
d.ID,
d.DepartmentName,
d.ParentID,
CAST(REPLICATE('-', 10 * r.Level) + d.DepartmentName AS NVARCHAR(MAX)),
r.Level + 1,
CONVERT(varchar(255), r.Path + RIGHT('0000' + CAST(d.ID AS varchar(4)), 4))
FROM
tblDepartment d
INNER JOIN RecursiveDepartment r ON d.ParentID = r.ID
)
SELECT
ROW_NUMBER() OVER (ORDER BY Path) AS number,
Tree
FROM
RecursiveDepartment
ORDER BY
Path;