What is the most effective and flexible way to generate combinations in TSQL?

Posted by SDReyes on Stack Overflow See other posts from Stack Overflow or by SDReyes
Published on 2010-05-10T17:22:33Z Indexed on 2010/05/10 17:24 UTC
Read the original article Hit count: 174

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

        .
        .
        .

© Stack Overflow or respective owner

Related posts about tsql

Related posts about sql-server