SQL Server 2017

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

CREATE TABLE #AccountLinks
(
	AccountRef				VARCHAR(10),
	RelationshipType		VARCHAR(10),
	RelationshipRef			VARCHAR(10),
	PrimaryRelationshipRef	VARCHAR(10)
)
INSERT INTO #AccountLinks
(
    AccountRef,
    RelationshipType,
    RelationshipRef,
    PrimaryRelationshipRef
)
VALUES
('2099134', 'PRIMARY', 'P_00034', 'P_00034'),
('2113256', 'PRIMARY', 'P_00044', 'P_00044'),
('2099143', 'SECONDARY', 'S_00034', 'P_00034'),
('2099152', 'SECONDARY', 'S_00035', 'P_00034'),
('2099161', 'SECONDARY', 'S_00036', 'P_00034'),
('1449346', 'SECONDARY', 'S_00049', 'P_00044'),
('0731048', 'SECONDARY', 'S_00047', 'P_00044'),
('1402792', 'SECONDARY', 'S_00048', 'P_00044'),
('8858908', 'PRIMARY', 'P_00037', 'P_00037'),
('8858917', 'SECONDARY', 'S_00039', 'P_00037'),
('8858926', 'SECONDARY', 'S_00040', 'P_00037'),
('0935714', 'PRIMARY', 'P_00510', 'P_00510'),
('0935723', 'SECONDARY', 'S_00713', 'P_00510'),
('0935741', 'SECONDARY', 'S_00715', 'P_00510'),
('1242998', 'PRIMARY', 'P_00455', 'P_00455'),
('1243004', 'SECONDARY', 'S_00715', 'P_00455'),
('8048567', 'PRIMARY', 'P_00331', 'P_00331'),
('8048576', 'SECONDARY', 'S_00448', 'P_00331'),
('9999999', 'PRIMARY', 'P_99999', 'P_99999')
19 rows affected

SELECT	al_p.AccountRef AS [PRIMARY], STRING_AGG(al_c.AccountRef, ', ') AS SECONDARIES
FROM	#AccountLinks al_p
			LEFT JOIN #AccountLinks al_c
				ON al_p.RelationshipRef = al_c.PrimaryRelationshipRef
				AND al_c.RelationshipType = 'SECONDARY'
WHERE	al_p.RelationshipType = 'PRIMARY'
GROUP BY al_p.AccountRef


PRIMARY
SECONDARIES


0935714
0935723, 0935741

1242998
1243004

2099134
2099143, 2099152, 2099161

2113256
1449346, 0731048, 1402792

8048567
8048576

8858908
8858917, 8858926

9999999
null


SELECT	al_p.AccountRef AS [PRIMARY]
	 , STUFF((SELECT	', ' + al_c.AccountRef
		FROM	#AccountLinks al_c
		WHERE	al_c.PrimaryRelationshipRef = al_p.RelationshipRef
			AND al_c.RelationshipType = 'SECONDARY'
		FOR XML PATH('')), 1, 2, '') AS SECONDARIES
FROM	#AccountLinks al_p
WHERE	al_p.RelationshipType = 'PRIMARY'


PRIMARY
SECONDARIES


2099134
2099143, 2099152, 2099161

2113256
1449346, 0731048, 1402792

8858908
8858917, 8858926

0935714
0935723, 0935741

1242998
1243004

8048567
8048576

9999999
null

PRIMARY	SECONDARIES
0935714	0935723, 0935741
1242998	1243004
2099134	2099143, 2099152, 2099161
2113256	1449346, 0731048, 1402792
8048567	8048576
8858908	8858917, 8858926
9999999	null