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),
(1000, null),
(2000, 1000)
11 rows affected
with tree as
(
select orgId, parentId,0 as tree_order, path = cast(orgId 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 | 100/ | 9 |
| 1000 | 0 | 1000/ | 2 |
| 2000 | 1 | 1000/2000/ | 1 |
| 129 | 1 | 100/129/ | 5 |
| 134 | 1 | 100/134/ | 3 |
| 133 | 2 | 100/134/133/ | 2 |
| 133 | 2 | 100/134/133/ | 2 |
| 439 | 2 | 100/129/439/ | 3 |
| 450 | 2 | 100/129/450/ | 1 |
| 501 | 3 | 100/129/439/501/ | 2 |
| 602 | 4 | 100/129/439/501/602/ | 1 |