By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Organization_structure (orgID int, parentID int);
INSERT INTO Organization_structure VALUES
(100, NULL),
(129, 100),
(134, 100),
(439, 129),
(450, 129),
(133, 134),
(133, 134),
(501, 439),
(602, 501);
9 rows affected
with tree as
(
select orgId, parentId,0 as tree_order, path = cast('root' as varchar(100))
from Organization_structure
where parentID is null
union all
select os.orgId, os.parentId, 1 + tree_order as tree_order,
path = cast(tree.path + '/' + right(('000000000' + os.orgId), 10) as varchar(100))
from Organization_structure os
join tree
on tree.orgId = os.parentId
)
select orgId, tree_order, path, t2.cnt
from tree
cross apply (select count(*) cnt from tree t1 where t1.path like tree.path + '%') t2
order by tree_order;
| orgId | tree_order | path | cnt |
|---|---|---|---|
| 100 | 0 | root | 9 |
| 129 | 1 | root/129 | 5 |
| 134 | 1 | root/134 | 3 |
| 133 | 2 | root/134/133 | 2 |
| 133 | 2 | root/134/133 | 2 |
| 439 | 2 | root/129/439 | 3 |
| 450 | 2 | root/129/450 | 1 |
| 501 | 3 | root/129/439/501 | 2 |
| 602 | 4 | root/129/439/501/602 | 1 |