May 17, 2011 at 9:28 am
Hi,
I have a list of around 40 random numbers that I need to find all combinations for and generate a table that has an ID column and another column with the number, e.g.:
Input:
Number
1
2
3
Output:
IDNumber
11
22
33
41
42
51
53
62
63
71
72
73
Can anybody help?
Thanks
May 17, 2011 at 9:32 am
Sounds like homework. What have you tried so far? You expected results don't match the input. Can you provide both input tables and then your attempts at it?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2011 at 9:49 am
Mike01 (5/17/2011)
Sounds like homework.
It sure does. Don't mean to point fingers, but as a former professor, it sure smells like an exercise to demonstrate/test understanding of cartesian products and/or reflexive queries.
If not, I'd be very interesting in a brief synopsis of the business reason for doing this. Thanks.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 17, 2011 at 9:56 am
There is only one input table, which is simply a list of numbers. The ID column in the output table represents the combination so if there are 1,000 combinations then the ID will go up to 1,000.
The business reason for this is that I have been given a table with groups (which are the 40 random numbers) and I know the output I require (from another system), which should be the sum of a certain muber of groups, but have not been provided with any rules regarding how the groups combine. I want to be able to test all combinations in one query rather than trying random ones manually.
Thanks
May 17, 2011 at 10:00 am
A cross join will give you all combinations.
Dropping into a new table with an identity property will give you a count. Or you could use ROW_NUMBER() to artificially generate these in a query.
It does sound like homework, but those hints should help you get moving. Post back if you don't understand, but I'd hope you're run a few queries and try things out.
May 17, 2011 at 10:16 am
When searching for examples many use CROSS JOIN but they are regarding combinations of two different column values, e.g. size and colour. Here is my example data again, hopefully a bit clearer this time:
Input:
Number
44
92
97
Output:
IDNumber
144
----------------
292
----------------
397
----------------
444
492
----------------
544
597
----------------
692
697
----------------
744
792
797
May 17, 2011 at 10:30 am
It's not clear. Are you saying all permutations of numbers from one table, including non-existent values?
Because 44 isn't a permutation that's a combination of your input set. However if you have 1,000 different values, then do you want 1000 singlets, then 1,000,000 double combinations, and 1,000^3 triplets? That's an unreasonable number to every check.
The problem with the results you've shown is that you're bending combinations from a row, where you might show a combination, into a series of rows, essentially an unpivot of the cross join.
Not sure how you would solve this if that's your intention.
May 17, 2011 at 10:50 am
Your second example of output is exactly the same as the first, the input numbers have been substituted. You example still makes it unclear what you are trying to achieve.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 17, 2011 at 11:03 am
I'm looking for combinations, not permutations since the order is unimportant.
In my example there are 3 single value combinations, 3 double value combinations and 1 triple value combination.
I changed the numbers in the input table so they wouldn't be confused with the ID values.
Here is another example showing all the fruit salads you could make from three different fruits:
Input:
Ingredient
Apple
Orange
Banana
Output:
RecipeIngredient
1Apple
2Orange
3Banana
4Apple
4Orange
5Apple
5Banana
6Orange
6Banana
7Apple
7Orange
7Banana
Thanks,
May 17, 2011 at 5:08 pm
Recipe number 7 looks a lot like recipe #1 to me... This problem seems oddly familiar though.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 18, 2011 at 3:04 am
No, recipie 1 is:
Apple
Recipie 7 is:
Apple
Orange
Banana
May 18, 2011 at 3:29 am
Using Excel I've just worked out that there will be 550 billion combinations for my 39 numbers:
=COMBIN(39,1)
.
.
.
.
=COMBIN(39,39)
Obviously this isn't practical for my purposes, thank you all for your help anyway.
May 18, 2011 at 10:11 am
Thanks for the update. I was thinking that it was an unwieldy amount of data for even small sets of data. 550B. Wow.
May 18, 2011 at 12:45 pm
ccparkhill (5/18/2011)
No, recipie 1 is:Apple
Recipie 7 is:
Apple
Orange
Banana
My error. I wasn't thinking of combinations of single elements.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply