PL is probably correct in the way this should be handled from a theoretical perspective.
On the other hand, this may work but also may not be particularly performant.
DECLARE @T1 TABLE (ITEM1 INT, ITEM2 INT);
INSERT INTO @T1
SELECT 0224180,0224181
UNION ALL SELECT 0224180,0224190
UNION ALL SELECT 0224181,0224180
UNION ALL SELECT 0224181,0224190
UNION ALL SELECT 0224190,0224180
UNION ALL SELECT 0224190,0224181
UNION ALL SELECT 0202294,0202295
UNION ALL SELECT 0202295,0202294
UNION ALL SELECT 0209250,0209251
UNION ALL SELECT 0209251,0209250
UNION ALL SELECT 1, 2
UNION ALL SELECT 1, 3
UNION ALL SELECT 1, 4
UNION ALL SELECT 2, 3
UNION ALL SELECT 2, 4
UNION ALL SELECT 3, 4
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 1
UNION ALL SELECT 3, 2
UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3
UNION ALL SELECT 7, 8
UNION ALL SELECT 7, 9
UNION ALL SELECT 8, 9
UNION ALL SELECT 8, 7
UNION ALL SELECT 9, 7
UNION ALL SELECT 9, 8
;
WITH rCTE AS
(
SELECT n=0, ITEM
,rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,ITEM1=ITEM
,ITEM2=NULL
FROM
(
SELECT DISTINCT ITEM=MIN(b.ITEM1)
FROM
(
SELECT ITEM1, ITEM2, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @T1
) a
CROSS APPLY
(
SELECT ITEM1=CASE WHEN ITEM1 < ITEM2 THEN ITEM1 ELSE ITEM2 END
,ITEM2=CASE WHEN ITEM1 < ITEM2 THEN ITEM2 ELSE ITEM1 END
FROM @T1 b
WHERE a.ITEM1 IN (b.ITEM1, b.ITEM2)
) b
GROUP BY rn
) a
UNION ALL
SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2
FROM rCTE a
JOIN @T1 b ON a.ITEM = b.ITEM1
WHERE n <= 1
UNION ALL
SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2
FROM rCTE a
JOIN @T1 b ON a.ITEM = b.ITEM2
WHERE n <= 1
)
SELECT DISTINCT ITEM1, ITEM2, ID=rn
FROM rCTE
WHERE n = 2
ORDER BY rn, ITEM1, ITEM2
Note that I added some additional sample data because I wanted to see how it would resolve out with an additional set of triplet product IDs and also a 4-tuple of product IDs.
I'm assuming you don't care which set of grouped products gets which ID. Just that each group is numbered independently.
Edit: Slightly simplified the query. Note that this may only work if all permutations appear in the adjacency list.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St