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

-- TC44_v1
--
-- Stefan_G

-- use recursion to generate all combinations of colors for each item
-- use recursion again to concatenate the items for each combination

;with
-- assign each color a row number in sort order
-- use pseudo-recursion to increase performance by materailizing this result
cte1 as (
	select
		ItemID, Color, row_number() over (partition by ItemID order by Color) as row
	from TC44 with (nolock)
	
	union all
	
	-- pseudo-recursion. This should not return any rows
	select * from cte1 where ItemID<0
)
-- use recursion to generate all combinations of colors for each item
, cte2 as (
	-- varchar(8000) should be enough. If we have so many colors that we need a longer string than that then the number of result rows would be impossibly high
	select ItemID, cast(Color as varchar(8000)) as Combination, row as maxrow, cast(1 as int) as colors
	from cte1
	
	union all
	
	select a.ItemID, a.Combination+'+'+b.Color, b.row as maxrow, colors+1 as colors
	from cte2 a
	join cte1 b
		on a.ItemID=b.ItemID and b.row > a.maxrow
)
-- add a unique number for each combination
, cte21 as (
	select * , dense_rank() over (order by colors, combination) as r, row_number() over (partition by colors, combination order by itemid) as r2
	from cte2
	
	union all

	-- pseudo-recursion	
	select * from cte21 where ItemID<0
)
-- concatenate items using recursion
, cte22 as (
	select colors, Combination, cast(ItemID as varchar(max)) as Items, r, r2, cast(0 as int) as itemcount
	from cte21
	where r2=1
	
	union all
	
	select p.colors, p.Combination, case when n2.itemid is null then p.Items else p.Items+','+cast(n2.ItemID as varchar(8000)) end as Items, n2.r, n2.r2, p.itemcount+1 as itemcount
	from cte22 p
	outer apply (select * from cte21 n where n.r=p.r and n.r2=p.r2+1) n2
	where p.r is not null
)
select combination as ColorCombination, itemcount as Occurrences, Items
from cte22
where r is null
order by colors, combination
option (maxrecursion 0)

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Jan  7 2011  1:38PM     1912718        3166        3454    3.595
  2 Jan  7 2011  2:58PM     1912209        3186        3484    3.644
  3 Jan  7 2011  4:18PM     1912225        3186        3484    3.645
  4 Jan  7 2011  5:39PM     1913286        3178        3422    3.615
  5 Jan  7 2011  6:59PM     1912223        3186        3500    3.646