SQL Server Recursion Question

I need to create links between pairs of items and rows of item pairs:

ItemA   ItemB
----------------
1       2
1       3
4       5
4       6
6       2  
7       8
9       2
9       10
11      12
11   开发者_运维问答   13
14      15

Matching on either side of a pair constitutes a link:

Link  A   B
---------------
1     1   2
1     1   3
1     4   5
1     4   6
1     6   2
2     7   8
1     9   2
1     9   10
3     11  12
3     11  13
4     14  15

The Link-Item relationship will be stored in the DB as:

Link  Item
--------------
1     1
1     2
1     3
1     4
1     5
1     6
1     9
1     10
2     7
2     8
3     11
3     12
3     13
4     14
4     15

Any ideas on the most efficient way to do this (SQL Server 2005)?

ItemA = 1 and ItemB = 2 is the first pair. There are 11 pairs in the set to process. Does that make sense?

I suspect that this question is dead, but here goes.

Judging by the comments, there may have been some misunderstanding of what is being done here. It looks like the Link table is being generated by the pairs. In effect, the Links represent a partition of the Items based on the relationship "is paired with".

Here is a partial answer (in the sense that it creates the Link table as illustrated but may not be the most efficient). Perhaps somebody can improve on this.

DECLARE @pairs TABLE (ItemA INT, ItemB INT)

INSERT INTO @pairs (ItemA, ItemB) VALUES ( 1,  2)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 1,  3)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 4,  5)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 4,  6)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 6,  2) 
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 7,  8)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 9,  2)
INSERT INTO @pairs (ItemA, ItemB) VALUES ( 9, 10)
INSERT INTO @pairs (ItemA, ItemB) VALUES (11, 12)
INSERT INTO @pairs (ItemA, ItemB) VALUES (11, 13)
INSERT INTO @pairs (ItemA, ItemB) VALUES (14, 15)

DECLARE @links TABLE (Link INT, Item INT)

DECLARE @nextItem INT
DECLARE @nextLink INT
SET @nextLink = 0

DECLARE @itemsLeft BIT
SET @itemsLeft = 1

DECLARE @insertCount INT

WHILE @itemsLeft = 1
BEGIN

    -- Get the next Item not already in a link
    SELECT @nextItem = MIN(allItems.Item) 
    FROM (SELECT ItemA AS Item FROM @pairs UNION SELECT ItemB FROM @pairs) AS allItems
    LEFT JOIN @links l ON l.Item = allItems.Item
    WHERE l.Link IS NULL

    SET @nextLink = @nextLink + 1

    IF (@nextItem IS NOT NULL)
    BEGIN

        -- There will be at least 1 new link            
        INSERT INTO @links (Link, Item) VALUES (@nextLink, @nextItem)

        SET @insertCount = 1
        -- Keep going until no new Items found...
        WHILE (@insertCount > 0)
        BEGIN

            INSERT INTO @links (Link, Item)
            SELECT la.Link, p.ItemB
            FROM @pairs p
            INNER JOIN @links la ON la.Item = p.ItemA
            LEFT JOIN @links lb ON lb.Item = p.ItemB
            WHERE lb.Link IS NULL
            SET @insertCount = @@ROWCOUNT

            INSERT INTO @links (Link, Item)
            SELECT lb.Link, p.ItemA
            FROM @pairs p
            INNER JOIN @links lb ON lb.Item = p.ItemB
            LEFT JOIN @links la ON la.Item = p.ItemA
            WHERE la.Link IS NULL
            SET @insertCount = @insertCount + @@ROWCOUNT

        END
    END
    ELSE
        SET @itemsLeft = 0

END

SELECT * FROM @links ORDER BY 1,2