Type of combination in TSQL
Hello,
If I have 4 values : A, B, C and D
I need a TSQL function that generate all the possible combination
of 2 values together.
Each generated record could not have 2 same values,
and the order is not important.
So, for the (A,B,C,D) values the result would be 6 records as the following:
A,B
A,C
A,D
B,C
B,D
C,D
How can i do this??
thanks
If I have 4 values : A, B, C and D
I need a TSQL function that generate all the possible combination
of 2 values together.
Each generated record could not have 2 same values,
and the order is not important.
So, for the (A,B,C,D) values the result would be 6 records as the following:
A,B
A,C
A,D
B,C
B,D
C,D
How can i do this??
thanks
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hello,
I added E to the temp table and the result is wrong!
it gave 7 records instead of 10.
A C
A E
B A
B E
C B
C E
D E
for instance A,D is missing...
I added E to the temp table and the result is wrong!
it gave 7 records instead of 10.
A C
A E
B A
B E
C B
C E
D E
for instance A,D is missing...
create table #temp(fld varchar(1))
insert into #temp
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E'
select fld1, fld2 from (
SELECT distinct t1.fld as fld1, t2.fld as fld2, ranking = dense_rank() over(partition by ascii(t1.fld) + ascii(t2.fld) order by newid())
FROM #temp t1
cross join #temp t2
where t1.fld <> t2.fld
) a
where ranking = 1
ASKER
If we have A,B
we don't want B,A....
thanks