SQL Server 2019

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

(No column name)
Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

create table PartyTable(PartyID int
 , PartyName varchar(50))
insert into PartyTable values
(14, 'CIDS'),
(15, 'DTL'),
(16, 'CRTS'),
(17, 'VIVAR'),
(18, 'CT INC'),
(19, 'AMC INC'),
(20, 'YIBG'),
(38, 'LLA INC'),
(35, 'GFRC'),
(43, 'UC AM'),
(43, 'MAIN RP'),
(44, 'CIDW'),
(46, 'KIN TECH')

create table ResponsibleParty (SPID int
, RPID int
, PartyKey int)
insert into ResponsibleParty values
(14,      38,    43),
(15 ,     38 ,   44),
(16  ,    38  ,  45),
(17   ,   38   , 45),
(18    ,  38    ,46),
(19     , 38    ,47),
(20      ,38    ,47)

select ResponsibleParty.RPID
 , subParty.PartyKey as PartyID
 , party.PartyName
 , PartyTable.PartyID as SubID
 , subPartyName.PartyName as SubParty
from ResponsibleParty
inner join PartyTable on ResponsibleParty.SPID = PartyTable.PartyID
inner join ResponsibleParty subParty on subParty.SPID = PartyTable.PartyID
left join PartyTable party on subparty.PartyKey = party.PartyID
left join PartyTable subPartyName on ResponsibleParty.SPID = subPartyName.PartyID

RPID	PartyID	PartyName	SubID	SubParty
38	43	UC AM	14	CIDS
38	43	MAIN RP	14	CIDS
38	44	CIDW	15	DTL
38	45	null	16	CRTS
38	45	null	17	VIVAR
38	46	KIN TECH	18	CT INC
38	47	null	19	AMC INC
38	47	null	20	YIBG