# Use SQL to Create All Possible Unique Combinations

Every solution I have seen required some control flow statements (such as looping) and often creation of a sproc or function. These are usually acceptable, but it really only requires a very simple SQL statement.

I’ll use the letters A-G for this example. I wrote this in TSQL, but it should work or be adaptable to almost any version.

```with T_VALUE as (
select 'A' as Value union
select 'B' union
select 'C' union
select 'D' union
select 'E' union
select 'F' union
select 'G'	)

select	*
from	T_VALUE A
cross join T_VALUE B
where	A.Value < B.Value;
``` Note that this solution does not require the list of values to be distinct, they must only be comparable:

```with T_VALUE as (
select 'A' as Value union
select 'B' union
select 'C' union
select 'C' union
select 'C' union
select 'C' union
select 'A'	)

select	*
from	T_VALUE A
cross join T_VALUE B
where	A.Value < B.Value;
``` So far we have only done unique pairs. You can truly do all unique combinations, but you have to create a cross-join query for every possible number of items in a combination. Because this set contains seven items, there can be up to seven items in a combination, which means seven cross-join queries total. It might get to be a bit much, but if your set is small you can get there with some copy-paste work.

Here’s an example (I reduced it to 4 items for brevity):

```with T_VALUE as (
select 'A' as Value union
select 'B' union
select 'C' union
select 'D' 	)

select	distinct
Value as Value_1,
null as Value_2,
null as Value_3,
null as Value_4
from	T_VALUE
union
select	A.Value,
B.Value,
null,
null
from	T_VALUE A
cross join T_VALUE B
where   A.Value < B.Value
union
select	A.Value,
B.Value,
C.Value,
null
from	T_VALUE A
cross join T_VALUE B
cross join T_VALUE C
where	A.Value < B.Value
and	B.Value < C.Value
union
select	A.Value,
B.Value,
C.Value,
D.Value
from	T_VALUE A
cross join T_VALUE B
cross join T_VALUE C
cross join T_VALUE D
where	A.Value < B.Value
and	B.Value < C.Value
and	C.Value < D.Value;
``` 