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),
(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

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