I have a table (quina) with 5 fields, each field can have the value of 1 to 80.
I would like to know if it is possible in SQL or only in Delphi, to get the dukes (combinations of 2 numbers) between 1 and 80 in the fields from c1 to c5. no matter where the values are on the line.
Example: combination of 01 and 02: 20 times .
(But 01 may be in c1, c2 or c3, and 02 may be in c4 or c5. The same number can not be repeated on the same line.)
- aligning
select c1 c from tabela
union all
select c2 c from tabela
union all
select c3 c from tabela
union all
select c4 c from tabela
union all
select c5 c from tabela
- reading as a table
select virtual1.*
from
(
select c1 c from tabela
union all
select c2 c from tabela
union all
select c3 c from tabela
union all
select c4 c from tabela
union all
select c5 c from tabela
) virtual1
- Forcing a Cartesian
select virtual1.*,virtual2.*
from
(
select c1 c from tabela
union all
select c2 c from tabela
union all
select c3 c from tabela
union all
select c4 c from tabela
union all
select c5 c from tabela
) virtual1,
(
select c1 c from tabela
union all
select c2 c from tabela
union all
select c3 c from tabela
union all
select c4 c from tabela
union all
select c5 c from tabela
) virtual2
where virtual1.c <. virtual2.c
The basic idea is to unpivot the table and then force a Cartesian.