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
27

129
0
root
27

134
0
root
27

439
0
root
27

450
0
root
27

133
0
root
27

133
0
root
27

501
0
root
27

602
0
root
27

1000
0
root
27

602
1
root/602
1

501
1
root/501
2

129
1
root/129
5

134
1
root/134
3

133
1
root/133
2

133
1
root/133
2

439
1
root/439
3

450
1
root/450
1

501
2
root/439/501
2

133
2
root/134/133
2

133
2
root/134/133
2

439
2
root/129/439
3

450
2
root/129/450
1

602
2
root/501/602
1

501
3
root/129/439/501
2

602
3
root/439/501/602
1

602
4
root/129/439/501/602
1

orgId	tree_order	path	cnt
100	0	root	27
129	0	root	27
134	0	root	27
439	0	root	27
450	0	root	27
133	0	root	27
133	0	root	27
501	0	root	27
602	0	root	27
1000	0	root	27
602	1	root/602	1
501	1	root/501	2
129	1	root/129	5
134	1	root/134	3
133	1	root/133	2
133	1	root/133	2
439	1	root/439	3
450	1	root/450	1
501	2	root/439/501	2
133	2	root/134/133	2
133	2	root/134/133	2
439	2	root/129/439	3
450	2	root/129/450	1
602	2	root/501/602	1
501	3	root/129/439/501	2
602	3	root/439/501/602	1
602	4	root/129/439/501/602	1