SQL Server : generate possible combinations of numbers

Question

I have a total universe of 41 numbers, and I'm trying to generate the possible combinations of 6 digits between these numbers and insert them into a table in SQL Server.

Could anyone give me a help to do this?

Thank you very much!

Answer 1

To generate all possible permutations (41!/(6!*(41-6)!) is just under 4.5 million) you could use

WITH Balls(N)
     AS (SELECT number
         FROM  master..spt_values
         WHERE type='P'
         AND number BETWEEN 1 AND 41)
SELECT *
FROM   Balls B1
       JOIN Balls B2
         ON B2.N > B1.N
       JOIN Balls B3
         ON B3.N > B2.N
       JOIN Balls B4
         ON B4.N > B3.N
       JOIN Balls B5
         ON B5.N > B4.N
       JOIN Balls B6
         ON B6.N > B5.N 

Answer 2

Store the numbers in a table, and use cross join six times to match this table with itself. If numbers cannot repeat, add where clauses, or inner join with on clause like on t3.num not in (t1.num,t2.num)

drop table #temp
GO
create table #temp (num int identity(1,1), x int)
GO
insert into #temp default values
GO 41

select 
    *
from #temp t1
cross join #temp t2
cross join #temp t3 
-- and so on