Check pairs of lottery numbers from archive

Question

I'm trying to find best possible solution to check pairs concerning to my national lottery. They draw six numbers. I have a database with all the previous drawings. The question is what is the most efficient table structure and the way of checking lottery number pairs'?

This is how my table looks:

year (float)    
date (nvarchar)    
draw (float)    
no1 (smallint)    
no2 (smallint)    
no3 (smallint)    
no4 (smallint)    
no5 (smallint)    
no6 (smallint)

I use this query to find a pair of numbers:

SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '36' IN (no1,no2,no3,no4,no5,no6)

I got the result 10 (the pair was draw out 10 times).

If I want to check the next pair, for example 1 and 37, I need to use the query again but with other numbers

SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '37' IN (no1,no2,no3,no4,no5,no6)

I got the result 13 (the pair was draw out 13 times)

Is there a SQL query (or some better solution) that can automatically check pairs in the number range from 1 to 90; example:

Pair 1 and 2
Pair 1 and 3
Pair 1 and 4
.
.

all the way up to pair 89 and 90?

Answer 1

Reading a little through the lines a little here, but a Tally seems to be the best choice here:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (90) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2)
SELECT COUNT(*) AS Draws,
       T1.I AS Number1,
       T2.I AS Number2
FROM dbo.Archive S
     CROSS JOIN Tally T1
     JOIN Tally T2 ON T1.I < T2.I
WHERE T1.I IN (no1,no2,no3,no4,no5,no6)
  AND T2.I IN (no1,no2,no3,no4,no5,no6)
GROUP BY T1.I, T2.I;