SQL Server 2017

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


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE A (
  Id INTEGER,
  RefNumber VARCHAR(4),
  OtherColumns VARCHAR(3)
);

INSERT INTO A
  (Id, RefNumber, OtherColumns)
VALUES
  ('101', 'A101', '...'),
  ('102', 'A102', '...');

CREATE TABLE B (
  Id INTEGER,
  RefNumber VARCHAR(4),
  OtherColumns VARCHAR(3)
);

INSERT INTO B
  (Id, RefNumber, OtherColumns)
VALUES
  ('201', 'B101', '...'),
  --('201', 'B101', '...'),
  ('202', 'B102', '...');

CREATE TABLE C (
  Id INTEGER,
  RefNumber VARCHAR(4),
  OtherColumns VARCHAR(3)
);

INSERT INTO C
  (Id, RefNumber, OtherColumns)
VALUES
  ('301', 'C101', '...'),
  ('302', 'C102', '...');

CREATE TABLE LinkedEntity (
  Id INTEGER,
  EntityId INTEGER,
  LinkedEntityId INTEGER
);

INSERT INTO LinkedEntity
  (Id, EntityId, LinkedEntityId)
VALUES
  ('1', '101', '202'),
    ('1', '101', '202'),
      ('1', '101', '202'),
        ('1', '101', '202'),
        ('1', '202', '101'),
  ('1', '101', '502'),
  ('2', '102', '301'),
  ('3', '102', '201'),
  ('4', '102', '202');
15 rows affected

SELECT
    A.Id,
    A.RefNumber,
    STRING_AGG(B.Id,',') as LinkedB,
    STRING_AGG(B.RefNumber,',') as LinkedBRefNumb,
    STRING_AGG(C.Id,',') as LinkedC,
    STRING_AGG(C.RefNumber,',') as LinkedCRefNumb
FROM 
    A
LEFT JOIN
    (
         SELECT EntityId, LinkedEntityId FROM LinkedEntity UNION ALL
         SELECT LinkedEntityId,EntityId FROM LinkedEntity 
    ) le ON A.Id = le.EntityId
LEFT JOIN
    B ON B.Id = le.LinkedEntityId
LEFT JOIN
    C ON C.Id = le.LinkedEntityId
GROUP BY
    A.Id,
    A.RefNumber


Id
RefNumber
LinkedB
LinkedBRefNumb
LinkedC
LinkedCRefNumb


101
A101
202,202,202,202,202
B102,B102,B102,B102,B102
null
null

102
A102
201,202
B101,B102
301
C101


SELECT
    AId as Id,
    ARefNumber as RefNumber,
    STRING_AGG(BId,',') as LinkedB,
    STRING_AGG(BRefNumber,',') as LinkedBRefNumb,
    STRING_AGG(CId,',') as LinkedC,
    STRING_AGG(CRefNumber,',') as LinkedCRefNumb
FROM (
    SELECT DISTINCT
        A.Id as AId,
        A.RefNumber as ARefNumber,
        B.Id as BId,
        B.RefNumber as BRefNumber,
        C.Id as CId,
        C.RefNumber as CRefNumber
    FROM 
        A
    LEFT JOIN
        LinkedEntity le ON A.Id IN (le.EntityId,le.LinkedEntityId)
    LEFT JOIN
        B ON B.Id IN  (le.EntityId,le.LinkedEntityId) 
    LEFT JOIN
        C ON C.Id IN  (le.EntityId,le.LinkedEntityId) 
) t
GROUP BY
    AId,
    ARefNumber


Id
RefNumber
LinkedB
LinkedBRefNumb
LinkedC
LinkedCRefNumb


101
A101
202
B102
null
null

102
A102
201,202
B101,B102
301
C101


SELECT A.Id, A.RefNumber,
  B.LinkedB, B.LinkedBRefNumb,
  C.LinkedC, C.LinkedCRefNumb
FROM dbo.A
OUTER APPLY 
(
  SELECT
    LinkedB        = STRING_AGG(B.Id, ','), 
    LinkedBRefNumb = STRING_AGG(B.RefNumber, ',')
  FROM dbo.LinkedEntity AS LB
  INNER JOIN dbo.B ON B.Id IN (LB.EntityId, LB.LinkedEntityId)
  WHERE A.Id IN (LB.EntityId, LB.LinkedEntityId)
) AS B
OUTER APPLY 
(
  SELECT
    LinkedC        = STRING_AGG(C.Id, ','), 
    LinkedCRefNumb = STRING_AGG(C.RefNumber, ',')
  FROM dbo.LinkedEntity AS LC
  INNER JOIN dbo.C ON C.Id IN (LC.EntityId, LC.LinkedEntityId)
  WHERE A.Id IN (LC.EntityId, LC.LinkedEntityId)
) AS C;


Id
RefNumber
LinkedB
LinkedBRefNumb
LinkedC
LinkedCRefNumb


101
A101
202,202,202,202,202
B102,B102,B102,B102,B102
null
null

102
A102
201,202
B101,B102
301
C101
Id	RefNumber	LinkedB	LinkedBRefNumb	LinkedC	LinkedCRefNumb
101	A101	202,202,202,202,202	B102,B102,B102,B102,B102	null	null
102	A102	201,202	B101,B102	301	C101