SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t_parent (DealRelationID INT NOT NULL, RelType INT, ParentID INT, ChildID INT)
INSERT INTO t_parent VALUES  (1001, 2, 101, 103)
INSERT INTO t_parent VALUES  (1002, 2, 102, 105)
INSERT INTO t_parent VALUES  (1003, 2, 103, 106)
INSERT INTO t_parent VALUES  (1004, 2, 104, 107)
INSERT INTO t_parent VALUES  (1005, 2, 105, 108)
INSERT INTO t_parent VALUES  (1006, 2, 106, 110)
INSERT INTO t_parent VALUES  (1007, 2, 107, 111)
INSERT INTO t_parent VALUES  (1008, 2, 111, 112)

CREATE TABLE #A (DealID INT, ParentCNT INT, ChildCNT INT)
INSERT INTO #A VALUES  (106, 0, 0)
INSERT INTO #A VALUES  (107, 0, 0)
INSERT INTO #A VALUES  (108, 0, 0)
INSERT INTO #A VALUES  (109, 0, 0)

select * from t_parent
select * from #A



DealRelationID
RelType
ParentID
ChildID


1001
2
101
103

1002
2
102
105

1003
2
103
106

1004
2
104
107

1005
2
105
108

1006
2
106
110

1007
2
107
111

1008
2
111
112




DealID
ParentCNT
ChildCNT


106
0
0

107
0
0

108
0
0

109
0
0


WITH rec4 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec4 AS r ON p.ParentID = r.ChildID

), rec5 (ParentID, ChildID)
AS 
(
    SELECT ParentID, ChildID
    FROM t_parent 
 -- where ChildID = 106
    UNION ALL
    SELECT p.ParentID, p.ChildID
    FROM t_parent AS p
        JOIN rec5 AS r ON p.ChildID = r.ParentID
)
--select ISNULL(a.DealID, 0), ttt.ParentCNT, ttt.ChildCNT
  --from #A a full join
  select ch.ParentID, ChildCNT, ParentCNT from (
select ParentID, COUNT(r5.ChildID) as ChildCNT from rec5 r5 Group by ParentID) ch
join (select ChildID, COUNT(r4.ParentID) as ParentCNT from rec4 r4 Group by ChildID) pr
on ch.ParentID = pr.ChildID--) ttt
--on a.DealID = ttt.ParentID


ParentID
ChildCNT
ParentCNT


103
2
1

105
1
1

106
1
2

107
2
1

111
1
2


with 
    children as (
        select parentid as dealid, parentid, childid from t_parent
        union all
        select r.dealid, p.parentid, p.childid
        from children r
        inner join t_parent p on p.parentid = r.childid
    ),
    parents as (
        select childid as dealid, parentid, childid from t_parent
        union all
        select r.dealid, p.parentid, p.childid
        from parents r
        inner join t_parent p on p.childid = r.parentid
    )
select coalesce(c.dealid, p.dealid) as dealid,
    count(distinct p.parentid) as parent_cnt,
    count(distinct c.childid)  as child_cnt
from children c
full join parents p on p.dealid = c.dealid
group by coalesce(c.dealid, p.dealid)


dealid
parent_cnt
child_cnt


101
0
3

102
0
2

103
1
2

104
0
3

105
1
1

106
2
1

107
1
2

108
2
0

110
3
0

111
2
1

112
3
0


Warning: Null value is eliminated by an aggregate or other SET operation.
DealRelationID	RelType	ParentID	ChildID
1001	2	101	103
1002	2	102	105
1003	2	103	106
1004	2	104	107
1005	2	105	108
1006	2	106	110
1007	2	107	111
1008	2	111	112