How to generate Combinations and Permutations using Self Join in SQL Server

SQL Server Self Join Query to generate Combinations and Permutations
The Combinations , Permutations are the Counting methods, generally we use in the Probability (the numerical measure of likelihood that an event will occur, whose values will always lies between "0" to "1") theory of Statistics, to Count the no.of experimental outcomes when the Experiment (the process that generates a well defined no.of outcomes) involves selection r objects from a Sample space (the set of all experimental outcomes) of N objects.
Permutations are for Lists (Order of selection matters) and Combinations are for Groups (Order of selection doesn’t matter).

Eg:
N!(N Factorial) = N*(N-1)*(N-2)….(2)*(1).
5!=5*4*3*2*1=120
0!=1
Now we will discuss about generating the Combinations and Permutations using the SQL Self Joins.
Suppose we have a Table (assume as Sample space) as follows with N=5 rows. We here assume here that no duplicated values in the Table.
Select * From [dbo].[tbl_Permu_Combi]

1) Combinations :
Now we can generate the Combinations of r=2 elements(in each Column) from N=5, using the below Query.
In Combinations we can take only one side Combination(no flip side). We assume here that (A,B)=(B,A) is duplicate so that we don't need the flipside.
--Combinations ( N=5, r=2)

SELECT Cast(T1.Id as Varchar(5))+ ',' + Cast(T2.Id as Varchar(5)) As Id_Combinations,
(T1.Str + ',' + T2.Str) As Str_Combinations
FROM [dbo].[tbl_Permu_Combi] T1
INNER JOIN [dbo].[tbl_Permu_Combi] T2
ON T1.Id < T2.Id
Order By T1.Id

Result :

Notes:

From the result we observed that 10 Combinations are generated as per the below logic of the Combinations in Statistics(Probability).

N=5 ; r=2

C(5,2) = (5 !)/(2!)(5-2)! = (5*4*3*2*1)/(2*1)(3!)=(120)/(2)(3*2*1)=(120)/12=10

In the similar manner we can generate a another Combination with r=3 , N=5 as follows..

--Combinations ( N=5, r=3)

SELECT Cast(T1.Id as Varchar(5))+ ',' + Cast(T2.Id as Varchar(5)) + ',' +
Cast(T3.Id as Varchar(5)) As Id_Combinations,
(T1.Str + ',' + T2.Str+ ',' + T3.Str) As Str_Combinations
FROM [dbo].[tbl_Permu_Combi] T1
INNER JOIN [dbo].[tbl_Permu_Combi] T2
ON T1.Id < T2.Id
INNER JOIN [dbo].[tbl_Permu_Combi] T3
ON T2.Id < T3.Id
Order By T1.Id

Result:

In the similar manner we can generate a another Combination with r=4, N=5 as follows..

--Combinations ( N=5, r=4)

SELECT Cast(T1.Id as Varchar(5))+ ',' + Cast(T2.Id as Varchar(5)) + ',' +
Cast(T3.Id as Varchar(5)) + ',' + Cast(T4.Id as Varchar(5)) As Id_Combinations,
(T1.Str + ',' + T2.Str + ',' + T3.Str + ',' + T4.Str) As Str_Combinations
FROM [dbo].[tbl_Permu_Combi] T1
INNER JOIN [dbo].[tbl_Permu_Combi] T2
ON T1.Id < T2.Id
INNER JOIN [dbo].[tbl_Permu_Combi] T3
ON T2.Id < T3.Id
INNER JOIN [dbo].[tbl_Permu_Combi] T4
ON T3.Id < T4.Id
Order By T1.Id

Result :

2) Permutations:
Now we can generate the Permutations of r=2 elements(in each Column) from N=5, using the below Query.

In Permutations we can take both the sides(flip side as well) of combination. We assume here that (A,B)<>(B,A) is not duplicate so that we will consider the flipside as well.

--Permutations ( N=5, r=2)
SELECT Cast(T1.Id as Varchar(5))+ ',' + Cast(T2.Id as Varchar(5)) As Id_Permutations,
(T1.Str + ',' + T2.Str) As Str_Permutations
FROM [dbo].[tbl_Permu_Combi] T1
INNER JOIN [dbo].[tbl_Permu_Combi] T2
ON T1.Id != T2.Id

Order By T1.Id

Notes:

From the result we observed that 20 Permutations are generated as per the below logic of the Permutations in Statistics(Probability).
N=5 ; r=2
P(5,2) = (5 !)/(5-2)! = (5*4*3*2*1)/(3!)=(120)/(3*2*1)=(120)/6=20

Conclusion :
You can modify and apply the above Combination/Permutation logics as per your Business Scenario.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------