|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-07-10 : 08:11:23
|
John writes "I'm using SS2K and SP3 (I think!), here is the problem,I need to generate all combinations of X factors which can be represented at Y(i) levels (i = 1...X), (i.e. the number of levels for each factor isn't necessarily the same), her is an example of the problem, i have a sequence (represented as a string) "ABCADBCD...", each letter in the sequence can be represented in one or more ways, e.g. A can be represented as A1, A2, A3, B as B1, B2, C as C1, C2, C3, etc.. for each representation, there is a Usage associated with it that can be shown as a percentage, e.g. for A, A1 is used 40%, A2 used 40%, and A3 is used 20%, etc.., let's say that there is a tie in usage for A1 and A2 as well as D1 and D2, that is, representing A is done as A1 40% of the time and A2 is 40% of the time, the same goes for D1 and D2, the remaining letters B and C have a 100% usage for B1 and C1, i need to generate all combinations of these representations (A, B, C, D), that is, i need the following:(A1, B1, C1, D1); (A1, B1, C1, D2); (A2, B1, C1, D1); (A2, B1, C1, D2)to add a twist to the problem, the number of factors and levels is not static, it can change at runtime, i've tried using CROSS JOIN as well as CUBE and haven't gotten either to work, the problem with the CROSS JOIN is that the factors and levels aren't static, and as for CUBE, i don't think i'm using it correctly, ideally, what i would like is a temp table that has an iteration column, a factor column, and a level column and would look something like this:Iter, Factor, Level1, A, 11, D, 12, A, 12, D, 23, A, 23, D, 14, A, 24, D, 2 including B and C is not needed because thier usage is obvious, it's only for those that have ties that i need to determine all combinations for, if this is too vague/incomprehensible let me know and i can provide more detail, any help is greatly appreciated, thanks!" |
|
Generating all Combos/Permutations