Recursion would usually be used for this Richard.
Try the following:
IF 0 = 1 BEGIN; -- Knock up some sample data
WITH MyData (A, B) AS (
SELECT 'ProdA', 'ProdB' UNION ALL
SELECT 'ProdB', 'ProdA' UNION ALL
SELECT 'ProdB', 'ProdC' UNION ALL
SELECT 'ProdC', 'ProdB' UNION ALL
SELECT 'ProdD', 'ProdE' UNION ALL
SELECT 'ProdE', 'ProdD')
SELECT * INTO #MyData FROM MyData; END;
-- Get rid of dupe pairs
WITH DedupedSet AS (
SELECT DISTINCT x.*
FROM #MyData
CROSS APPLY (
SELECT
A = CASE WHEN A < B THEN A ELSE B END,
B = CASE WHEN A < B THEN B ELSE A END
) x
-- use recursion to resolve the hierarchy
), rCTE AS (
SELECT d.A, d.B, Seq = CAST(A+'>'+B AS VARCHAR(50)), [Level] = 0
FROM DedupedSet d
WHERE NOT EXISTS (SELECT 1 FROM DedupedSet di WHERE di.B = d.A)
UNION ALL
SELECT nl.A, nl.B, Seq = CAST(ll.Seq +'>'+ nl.B AS VARCHAR(50)), [Level] = ll.[Level]+1
FROM rCTE ll
INNER JOIN DedupedSet nl
ON nl.A = ll.B
)
SELECT Seq
FROM rCTE ro
WHERE NOT EXISTS (SELECT 1 FROM rCTE ri WHERE ri.[Level] > ro.[Level] AND ri.Seq LIKE ro.Seq+'%')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden