OK, here's my idea. I am still using a cursor to define the product combos. However, once defined, the combo list only needs to be changed if products are added or dropped.

First, assume the products are in #tmp_prod, and that the prod column will identify them in the response table.

----------

create table #prod_combo

(combo_id int, prod char(1))

go

declare @counter int

declare @cur_prod char(1)

declare @max_id int

set @counter = 1

declare c1 cursor for select prod from #tmp_prod

open c1

fetch c1 into @cur_prod

if (@@FETCH_STATUS = 0)

BEGIN

insert into #prod_combo VALUES (1, @cur_prod)

fetch c1 into @cur_prod

END

while (@@FETCH_STATUS = 0)

begin

select @max_id = MAX(combo_id) from #prod_combo

insert into #prod_combo select combo_id + @max_id, @cur_prod from #prod_combo

UNION select combo_id + @max_id, prod from #prod_combo

UNION select (2 * @max_id) + 1, @cur_prod

fetch c1 into @cur_prod

end

close c1

deallocate c1

----------

We create a flat table to hold all the possible product combinations. Each combination has a unique ID, and one row for each product in that combination.

It makes life a little easier to have a table containing the distinct combo IDs:

----------

select distinct combo_id into #tmp_combo_ids from #prod_combo

----------

Once we have this, processing the responses becomes easy. Given the following response table:

----------

create table #tmp_resp

(resp_id int, prod char(1), yn char(3))

go

insert into #tmp_resp

select 1, 'A', 'Yes'

UNION

select 1, 'B', 'No'

UNION

select 1, 'C', 'Yes'

UNION

select 2, 'A', 'Yes'

UNION

select 2, 'B', 'Yes'

UNION

select 2, 'C', 'No'

UNION

select 3, 'A', 'Yes'

UNION

select 3, 'B', 'No'

UNION

select 3, 'C', 'Yes'

UNION

select 4, 'A', 'Yes'

UNION

select 4, 'B', 'Yes'

UNION

select 4, 'C', 'Yes'

----------

we can process the responses with the following statement:

----------

select m.combo_id, count(distinct r.resp_id) AS "Would Buy", (select count(*) from #prod_combo where combo_id = m.combo_id) as "Products in Combo"

from #tmp_combo_ids m, #tmp_resp r

where r.yn = 'Yes'

and r.prod in (select prod from #prod_combo where combo_id = m.combo_id)

group by m.combo_id

----------

which yields:

combo_id Would Buy Products in Combo

1 4 1

2 4 2

3 2 1

4 4 2

5 4 3

6 4 2

7 3 1

I'll leave manipulating these results to find the best combo as an exercise for the reader 🙂

RD Francis