Not sure this is exactly what will work for you but it may be close. Note that I added a PN (STC3333) to see if it would put it at the beginning of the chain (oldest).

CREATE TABLE #Supersessions

(

PartNo VARCHAR(20)

,PNPrior VARCHAR(20)

);

INSERT INTO #Supersessions

SELECT 'RTC5756', 'STC9191'

UNION ALL SELECT 'SFP500160','STC9191'

UNION ALL SELECT 'STC9191','STC2951'

UNION ALL SELECT 'STC3765','STC9191'

UNION ALL SELECT 'STC8572','STC9191'

UNION ALL SELECT 'STC9150','STC9191'

UNION ALL SELECT 'STC8572','STC3333';

CREATE TABLE #Source

(

PartNo VARCHAR(20)

,PartDesc VARCHAR(100)

,Price MONEY

);

INSERT INTO #Source

SELECT 'SFP500160','KIT BRAKE LINING',58.02;

DECLARE @N INT = 1;

SELECT n=@N, OriginalPart=b.PartNo, a.PartNo, PNPrior

INTO #Hierarchy

FROM #Supersessions a

JOIN #Source b ON a.PartNo = b.PartNo;

WHILE @@ROWCOUNT > 0

BEGIN

SELECT @N = @N + 1;

INSERT INTO #Hierarchy

SELECT @N, OriginalPart, PartNo, PNPrior

FROM

(

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PNPrior = b.PNPrior

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PNPrior = b.PartNo

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PartNo = b.PNPrior

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PartNo = b.PartNo

EXCEPT

SELECT OriginalPart, PartNo, PNPrior

FROM #Hierarchy

) a;

END

--SELECT *

--FROM #Supersessions;

SELECT PartNo, PartDesc, Price

,Supercessions=REPLACE(STUFF(

(

SELECT '-' + PN

FROM

(

SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N)

,orb=CASE WHEN OriginalPart = PN THEN 0 ELSE N END, N

FROM #Hierarchy a

CROSS APPLY (VALUES (PartNo), (PNPrior)) b (PN)

) b

WHERE rn = 1 AND a.PartNo = b.OriginalPart

ORDER BY orb DESC

FOR XML PATH('')

), 1, 1, ''), '-', ' > ')

FROM #Source a;

GO

DROP TABLE #Source;

DROP TABLE #Supersessions;

DROP TABLE #Hierarchy;

There are several key issues here:

1. The need to eliminate a PN from the chain when it's already been resolved, hence I switched from the recursive CTE to the WHILE LOOP with EXCEPT.

2. At a particular level (and all of your PNs just below STC9191 fall into this category), I am not sure of the sort criteria. Perhaps you've got another column (like a date the PN was introduced) to help here.

3. It was not real clear from your initial description whether your Supersessions table lists the prior PN in the second column or whether those columns are reversed. I called that column PNPrior based on my assumption.

Edit: Oh yeah, and one more thing.

4. I'm not clear if you're trying to resolve a PN passed in by the UI (like @PartNo) or whether you always want to resolve from the Source table. I think I may have gotten that part wrong. But you should be able to work around that by using @PartNo in a WHERE clause of the first SELECT INTO #Hierarchy (instead of the JOIN).