• 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+'%')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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