Finding all possible combinations

Question

Finding all possible combinations

ssrat

Newbie

Points: 3
More actions
January 23, 2003 at 9:22 am

#159258

I have a table that looks like the first table below and I need to insert records into a second table with all possible combinations of the order details for each order. Any ideas on how to do this without declaring a cursor and marching the first table?
Possible purchased product for ordered product
OrdKey OrdDtl OrdProd PrchProd
165578 174673 3 3
166578 175279 1 1
165578 175279 1 2
165578 175279 1 5
165578 175279 1 9
165579 174674 3 3
165579 174675 8 1
Possible combinations of purchased products
Combo OrdKey OrdDtl OrdProd PrchProd
1 165578 174673 3 3
1 165578 175279 1 1
2 165578 174673 3 3
2 165578 175279 1 2
3 165578 174673 3 3
3 165578 175279 1 5
4 165578 174673 3 3
4 165578 175279 1 9
1 165579 174674 3 3
1 165579 174675 8 1

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

Of course, it is possible to avoid cursors. However I cannot figure out how you built the second table. Could you be more specific? What was the pattern?

ssrat Newbie Points: 3 More actions · Answer 2

Here is some background. When a user enters an order he can select from one to many ordered products to include on the order. We can actually purchase an ordered product as one to many purchase products. The first table is an expansion of all of the possible order detail lines where the OrdProd entry is the actual ordered product and the PrchProd entry is one of the ways that we can purchase that ordered product. I need the second table to be all of the possible combinations of purchased product for the ordered product. In the example tables there are two orders. In the first order the first ordered product can only be purchased one way the second ordered product can be purchased two ways and the third ordered product can be purchased two ways. This gives me four possible ways to purchase the three ordered products. The second order has two ordered products that can both only be purchased one way so I only get one possible way to purchase those two ordered products. I can generate the first table but I need to be able to generate the combinations in the second table for one to many orders at a time. This is driving me crazy. Hope this explanation helps.

NPeeters SSChampion Points: 12612 More actions · Answer 3

Oh boy oh boy. This one's a challenge

The following query returns a recordset with the correct records in it.



select o3.ordkey, count(distinct(o3.ordprod)) as modulo from (

select o1.ordkey, o1.orddtl, o1.ordProd, o1.prchprod

from orders o1 inner join orders o2

on o1.ordkey = o2.ordkey and o1.ordprod <> o2.ordprod

The remaining problem is assigning the correct COMBO number to each record. Can't seem to find a solution for it. Think it can be done using an IDENTITY in a temp table, and some juggling with start and end values though.

NPeeters SSChampion Points: 12612 More actions · Answer 4

Here's a more complete solution...



--Create a temp table, with an identity

SELECT IDENTITY(int,1,1) as combo_temp,

       o1.ordkey,

       o1.orddtl,

       o1.ordProd,

       o1.prchprod

INTO #temporders

FROM orders o1 INNER JOIN orders o2

     ON     o1.ordkey = o2.ordkey

        AND o1.ordprod <> o2.ordprod

ORDER BY o1.ordkey, o1.ordprod

-- Subtract the minimal identity value from combo_temp

-- to get 1 based numbering

SELECT (t1.combo_temp - t2.min_combo + 1) as COMBO,

        t1.ordkey,

        t1.orddtl,

        t1.ordprod,

        t1.prchprod

FROM #temporders t1 INNER JOIN

     (SELECT ordkey, ordprod,

             MIN(combo_temp) AS min_combo

      FROM #temporders

      GROUP BY ordkey, ordprod) t2

   ON t1.ordkey  = t2.ordkey AND

      t1.ordprod = t2.ordprod

ORDER BY t1.ordkey,

         t1.combo_temp - t2.min_combo,

         t1.ordprod

ssrat Newbie Points: 3 More actions · Answer 5

Close but no cigar. I already tried that. It works fine when there are only two details on an order since you are just getting a cross join where the ordered product is different. This won't work with three details though. Add another detail entry to the first order with a couple of possible purchase products and see what it does. I think I've got a recursive solution that will accomplish what I need but it's going to be slow as it will have to march the orders then recursively traverse all of the order details. I was hoping someone could steer me towards a beter solution.

NPeeters SSChampion Points: 12612 More actions · Answer 6

See your point. Next time, I'll try to investigate a bit further.

Still on it, but I don't think my employer will like me spending his time on this right now. I'll get back to you tomorrow, I hope with a solution.

NPeeters SSChampion Points: 12612 More actions · Answer 7

I've been banging my head against the wall all weekend, but to no avail.

Don't seem to get this one nailed down without using cursors... and even with cursors, it's not a trivial task.