Nyana22

asked on

# 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