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;

unique pairs.png

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;

unique-pairs-figure-2

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;

all-unique-combinations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s