SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table temp (
     Pos int
    ,Child nvarchar(18)
    ,Parent nvarchar(18)
    ,Test int
);

insert into temp (Pos, Child, Parent, Test)
values
(1, 'A', NULL, 1),
(2, 'J', NULL, 10),
(3, 'P', NULL, 16),
(4, 'Y', NULL, 25),
(1, 'B', 'A', 2),
(2, 'E', 'A', 5),
(1, 'C', 'B', 3),
(2, 'D', 'B', 4),
(1, 'F', 'E', 6),
(2, 'G', 'E', 7),
(1, 'H', 'G', 8),
(2, 'I', 'G', 9),
(1, 'K', 'J', 11),
(2, 'L', 'J', 12),
(3, 'M', 'J', 13),
(1, 'N', 'M', 14),
(2, 'O', 'M', 15),
(5, 'Z', NULL, 26),
(1, 'Q', 'P', 17),
(2, 'S', 'P', 19),
(3, 'T', 'P', 20),
(4, 'X', 'P', 24),
(1, 'R', 'Q', 18),
(1, 'U', 'T', 21),
(2, 'V', 'T', 22),
(3, 'W', 'T', 23)
26 rows affected

SELECT * FROM temp


Pos
Child
Parent
Test


1
A
null
1

2
J
null
10

3
P
null
16

4
Y
null
25

1
B
A
2

2
E
A
5

1
C
B
3

2
D
B
4

1
F
E
6

2
G
E
7

1
H
G
8

2
I
G
9

1
K
J
11

2
L
J
12

3
M
J
13

1
N
M
14

2
O
M
15

5
Z
null
26

1
Q
P
17

2
S
P
19

3
T
P
20

4
X
P
24

1
R
Q
18

1
U
T
21

2
V
T
22

3
W
T
23


WITH MyCTE AS (
SELECT *, 1 AS Level
FROM temp
WHERE Parent IS NULL

UNION ALL

SELECT t.Pos, t.Child, t.Parent, t.Test, MyCTE.Level+1 AS Level
FROM temp AS t
INNER JOIN MyCTE
ON t.Parent = MyCTE.Child
WHERE t.Parent IS NOT NULL)
SELECT MyCTE.*, CASE WHEN Offsprings.Offspring IS NULL THEN 1 ELSE Offsprings.Offspring END AS Offspring
FROM MyCTE
LEFT JOIN (
SELECT Parent, COUNT(Parent) AS Offspring
FROM temp
GROUP BY Parent)Offsprings
ON MyCTE.Child = Offsprings.Parent
ORDER BY MyCTE.Child


Pos
Child
Parent
Test
Level
Offspring


1
A
null
1
1
2

1
B
A
2
2
2

1
C
B
3
3
1

2
D
B
4
3
1

2
E
A
5
2
2

1
F
E
6
3
1

2
G
E
7
3
2

1
H
G
8
4
1

2
I
G
9
4
1

2
J
null
10
1
3

1
K
J
11
2
1

2
L
J
12
2
1

3
M
J
13
2
2

1
N
M
14
3
1

2
O
M
15
3
1

3
P
null
16
1
4

1
Q
P
17
2
1

1
R
Q
18
3
1

2
S
P
19
2
1

3
T
P
20
2
3

1
U
T
21
3
1

2
V
T
22
3
1

3
W
T
23
3
1

4
X
P
24
2
1

4
Y
null
25
1
1

5
Z
null
26
1
1

Pos	Child	Parent	Test
1	A	null	1
2	J	null	10
3	P	null	16
4	Y	null	25
1	B	A	2
2	E	A	5
1	C	B	3
2	D	B	4
1	F	E	6
2	G	E	7
1	H	G	8
2	I	G	9
1	K	J	11
2	L	J	12
3	M	J	13
1	N	M	14
2	O	M	15
5	Z	null	26
1	Q	P	17
2	S	P	19
3	T	P	20
4	X	P	24
1	R	Q	18
1	U	T	21
2	V	T	22
3	W	T	23