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

WITH Combinations AS
(	SELECT ItemID, convert(varchar(max), Color) AS ColorCombination, Color AS lastColor, 1 AS colorCount
	FROM TC44
UNION ALL
	SELECT TC44.ItemID, C.ColorCombination+'+'+TC44.Color, TC44.color, C.colorCount+1
	FROM TC44
	INNER JOIN Combinations C on TC44.ItemID=C.ItemID
	WHERE C.lastColor < TC44.Color
),
DistinctCombinations AS
(	SELECT ColorCombination, ColorCount, COUNT(*) AS Occurences
	FROM Combinations
	GROUP BY ColorCombination, ColorCount
)
SELECT ColorCombination, Occurences, 
	stuff(
	(   SELECT ','+ convert(varchar(max), ItemID)
		FROM Combinations C
		WHERE C.ColorCombination = DistinctCombinations.ColorCombination
		ORDER BY ItemID
		FOR XML PATH('')
	), 1, 1, '') AS Items 
FROM DistinctCombinations 
ORDER BY ColorCount, ColorCombination

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jan  7 2011  1:42PM     1656639        1835       59703   59.769
  2 Jan  7 2011  3:02PM     1656643        1835       60156   60.247
  3 Jan  7 2011  4:22PM     1656642        1835       59110   59.176
  4 Jan  7 2011  5:42PM     1656642        1835       59250   59.280
  5 Jan  7 2011  7:03PM     1656645        1835       59672   59.727