SQL Server 2017

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);
10 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
10

1000
0
root
10

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

orgId	tree_order	path	cnt
100	0	root	10
1000	0	root	10
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