What is the most effective and flexible way to generate combinations in TSQL?
- by SDReyes
What is the most effective and flexible way to generate combinations in TSQL?
With 'Flexible', I mean you should be able to add easily combination rules. e.g.: to generate combinatories of 'n' elements, sorting, remove duplicates, get combinatories where each prize belongs to a different lottery, etc.
For example, Having a set of numbers representing lottery prizes.
Number | Position | Lottery
---------------------------
12 | 01 | 67
12 | 02 | 67
34 | 03 | 67
43 | 01 | 89
72 | 02 | 89
33 | 03 | 89
(I include the position column because, a number could be repeated among different lottery's prizes)
I would like to generate combinatories like:
Numbers | Lotteries
-------------------
12 12 | 67 67
12 34 | 67 67
12 34 | 67 67
12 43 | 67 89
12 72 | 67 89
12 33 | 67 89
.
.
.
