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

-- TSQL Challange 44
-- Kevan Riley v4
--

;with 
cte(itemID, Color, lastcoloradded, NumColors) as
(
	select cast(itemID as varchar(max)), cast(color as varchar(max)), color, 1 from TC44
	union all
	select cte.itemID, cte.color  + '+' + c2.color, c2.color, NumColors+1
	from cte
	join TC44 c2 on c2.itemID = cte.itemID and c2.color > cte.lastcoloradded
)

select 
	color as ColorCombination,
	count(*) as Occurrences, 
	STUFF ((SELECT ',' + itemID FROM cte c2 WHERE c2.color = c1.color order by cast(c2.itemid as int) FOR XML PATH ('')),1,1,'') as Items
from cte c1
group by color
order by max(numcolors),color

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jan  7 2011  1:02PM     1833577         654       60812   60.858
  2 Jan  7 2011  2:22PM     1833573         654       59562   59.598
  3 Jan  7 2011  3:42PM     1833572         654       59766   59.862
  4 Jan  7 2011  5:02PM     1833571         654       60047   60.105
  5 Jan  7 2011  6:22PM     1833576         654       59938   59.953