get query records' combinations

[cUrLiTo]

Hi all,

This is my 1st post and I really hope the question hasn't alredy been posted cause i did a search and found nothin'.

What I need is to get the combinations of the records I get from a SQL Query.

For example:

If the query's records are:

record01
record02
record03
record04

I want to also output:

record01
record02
record03

&

record01
record02
record04

&

record01
record03
record04

&

record02
record03
record04

I need the same thing for the 2 records combinations as well..

record01
record02

&

record01
record03

and goes on...(6 combinations of 2 records out of total4)

The query's length isn't always 4. It can be from 1 to infinite.

Any help appreciated.

[Stressed_Simon]

You need to use the cartesian product, if your table was called temp_test and the field was called myID then you could use the following:-

SELECT t1.myID AS [value], t2.myID AS [order]
FROM temp_test t1, temp_test t2
WHERE t1.myID <> t2.myID
ORDER BY t2.myID ASC

Then you can use a grouped output on order.

[cUrLiTo]

Thanks for the answer Simon, but the code u provide is used for giving me the combinations of 2 records only.

If my total records are x, what about the combinations of (x-1), (x-2),...,2 records?

Also the query you wrote gives me:

record01
record02

&

record02
record01

I only want this pair once.

[Stressed_Simon]

Actually it will cope with any number however, you are correct about the repeated groupings. I'll think on it, but you are asking for something quite complex.