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

;with cte as
(
	select
		a.ItemID
		,cast(a.Color as varchar(8000)) mix
		,a.Color Color
		,cast(1 as tinyint) level
	from TC44 a

	union all

	select
		c.ItemID
		,cast(c.mix + '+' + a2.Color as varchar(8000)) mix
		,a2.Color Color
		,c.level + cast(1 as tinyint) level
	from cte c
	inner join TC44 a2 on c.ItemID = a2.ItemID and c.Color < a2.Color
)

select
	c.mix ColorCombination
	,count(*) Occurences
	,case when count(*) =  1 then cast(min(c.ItemID) as varchar(8000))
			when count(*) =  2 then cast(min(c.ItemID) as varchar(8000)) + ',' + cast(max(c.ItemID) as varchar(8000))
			else 
				stuff((select ',' + cast(c2.ItemID as varchar(8000)) + ''
					from cte c2
					where c.level = c2.level and c.mix = c2.mix
					group by c2.ItemID
					order by c2.ItemID
					for xml path(''))
				,1,1,'')
		end Items
from cte c
group by c.level,c.mix
order by c.level,c.mix
option(maxrecursion 0)

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jan  7 2011  1:14PM      767412         393       11484   11.518
  2 Jan  7 2011  2:34PM      767443         416       11000   11.058
  3 Jan  7 2011  3:54PM      767443         416       11312   11.407
  4 Jan  7 2011  5:14PM      767443         416       11500   11.544
  5 Jan  7 2011  6:34PM      767443         416       11281   11.328