Solution to TSQL Challenge 44 – Identify products that belong to each unique color combinations By Mark

-- Straightforward recursive solution
WITH Recur AS (
 SELECT ItemID,
        Color,
        CAST(Color AS VARCHAR(MAX)) AS ColorCombination,
        1 AS NumColors
 FROM TC44
 
 UNION ALL

 SELECT t.ItemID,
        t.Color,
        r.ColorCombination + CAST('+'+t.Color AS VARCHAR(MAX)),
        r.NumColors+1
 FROM TC44 t
 INNER JOIN Recur r ON r.ItemID=t.ItemID
                   AND t.Color > r.Color)
SELECT t1.ColorCombination,
       COUNT(*) AS Occurrences,
       STUFF((SELECT ','+CAST(t2.ItemID AS VARCHAR(10)) AS "text()"
              FROM Recur t2
              WHERE t2.ColorCombination=t1.ColorCombination
              ORDER BY t2.ItemID
              FOR XML PATH('')),1,1,'') AS Items
FROM Recur t1
GROUP BY t1.ColorCombination
ORDER BY MIN(t1.NumColors),t1.ColorCombination;

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jan  7 2011  1:15PM     1636255         602       58140   58.198
  2 Jan  7 2011  2:34PM     1636296         617       58406   58.475
  3 Jan  7 2011  3:55PM     1636297         617       57953   58.044
  4 Jan  7 2011  5:15PM     1636303         617       58469   58.471
  5 Jan  7 2011  6:35PM     1636297         617       58547   58.722