DECLARE @ClassID TABLE (Class INT, ID INT)DECLARE @ProductClass TABLE(Product INT, Class INT)INSERT INTO @ClassIDSELECT 1654, 1UNION ALL SELECT 1655, 1INSERT INTO @ProductClassSELECT 538, 1654UNION ALL SELECT 539, 1654UNION ALL SELECT 540, 1655UNION ALL SELECT 541, 1655

-- Solution 1: Combine the products ;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 2)SELECT [Group ID]=ROW_NUMBER() OVER (ORDER BY Product2, Product) ,[Product Group]=TuplesFROM UNIQUEnTuplesWHERE n=2

-- Solution 2: Now split out the results of the above by group;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 2)SELECT Product=item, [Group ID]FROM ( SELECT [Group ID]=ROW_NUMBER() OVER (ORDER BY Product2, Product) ,[Product Group]=Tuples FROM UNIQUEnTuples WHERE n=2) aCROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')

-- Solution 3: List combinations up to 3;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 AND n < 3)SELECT [Group ID]=CASE n WHEN 3 THEN 1 ELSE 2 END, [Item Group ID]=[Group ID], Product=itemFROM ( SELECT [Group ID]=ROW_NUMBER() OVER (PARTITION BY n ORDER BY Product2, Product) ,[Product Group]=Tuples, n FROM UNIQUEnTuples WHERE n IN (2,3)) aCROSS APPLY dbo.DelimitedSplit8K([Product Group], '+')ORDER BY CASE n WHEN 3 THEN 1 ELSE 2 END

-- Solution 4: Generate all combinations;WITH UNIQUEnTuples (n, Class, Product, Tuples, Product2) AS ( SELECT 1, Class, Product ,CAST(Product AS VARCHAR(MAX)), NULL FROM @ProductClass UNION ALL SELECT 1 + n.n, t.Class, t.Product ,CAST(t.Product AS VARCHAR(MAX)) + '+' + n.Tuples ,n.Product FROM @ProductClass t JOIN UNIQUEnTuples n ON CAST(t.Product AS VARCHAR(MAX)) < n.Tuples AND n.Class <> t.Class WHERE CHARINDEX(CAST(t.Product AS VARCHAR(MAX)), n.Tuples) = 0 )SELECT *FROM UNIQUEnTuples