CODEWITHSUNDEEP
sql-servert-sql
jaffa
jaffa

Reputation: 27350

Return all possible combinations of values on columns in SQL

How do I return a list of all combinations of values in 2 columns so they are new rows in T-SQL?

e.g.

Col1, Col2
----  ----
1     2
1     4
1     5

and turn this into all combinations:

1     2
1     4
1     5
2     4
2     5
4     5

Upvotes: 47

Views: 136873

Answers (8)

Casual user
Casual user

Reputation: 49

I think this has been over complicated! Just:

SELECT distinct Col1, Col2
FROM MyTable

to get all possible combinations..

Upvotes: 4

Josh
Josh

Reputation: 95

I was looking for something that would do this using only the SQL available to Microsoft Access 2016. I ended up figuring out something that others may find useful. This code utilizes CROSS JOIN so I found that it is necessary to split the two columns into two separate tables (each with one column). The AND statement forces one column to be less than the other, thereby eliminating any repetitive 1-2, 2-1 sort of occurrences.

SELECT DISTINCT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Column1 <> Table2.Column1
AND Table2.Column1 < Table1.Column1;

Upvotes: 9

Faheem Ahmad
Faheem Ahmad

Reputation: 64

Making Joe Answer easier

declare @t1 table  (col1 varchar(5))
insert @t1 
    select 'A' UNION
    select 'B' UNION
    select 'C' 


declare @t2 table  (col2 varchar(5))
insert @t2
    select '1' UNION
    select '2' UNION
    select '3' 


;with cteAllColumns as (
    select col1 as col
        from @t1
    union
    select col2 as col
        from @t2
)
select c1.col, c2.col 
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    order by c1.col, c2.col

verify your combinations Qty (No of rows) http://www.calculatorsoup.com/calculators/discretemathematics/combinations.php

Upvotes: -1

StinkySocks
StinkySocks

Reputation: 924

I find an inner join more intuitive because I use it more frequently than a cross join:

;with cteAllColumns as (
select col1 as col
    from YourTable
union
select col2 as col
    from YourTable
) 

select c1.col, c2.col 
from cteAllColumns c1 
    join cteAllColumns c2 on 1=1
where c1.col < c2.col
order by c1.col, c2.col

Upvotes: -1

Jamiec
Jamiec

Reputation: 136074

This uses 2 cte's, the first simply reproduces your input table, the second turns both columns into a single column. The final select crossjoin's this set to itself to produce the required output

with t(c1,c2)
AS
(
    select 1,2
    union select 1,4
    union select 1,5
)
,t2(c)
as
(
    select c1 from t
    union select c2 from t
)
select t2_1.c, t2_2.c
from t2 t2_1 
cross join t2 t2_2
where t2_1.c<t2_2.c
order by t2_1.c

Upvotes: 1

Bob Black
Bob Black

Reputation: 2405

You could cartesian join the table to itself, which would return all combinations of both columns.

select 
    distinct
    t1.Col1,
    t2.Col2
from 
    MyTable t1,
    MyTable t2

Upvotes: 49

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

Assuming at least SQL 2005 for the CTE:

;with cteAllColumns as (
    select col1 as col
        from YourTable
    union
    select col2 as col
        from YourTable
)
select c1.col, c2.col 
    from cteAllColumns c1 
        cross join cteAllColumns c2 
    where c1.col < c2.col
    order by c1.col, c2.col

Upvotes: 51

JNK
JNK

Reputation: 65147

You can do a self cross join...

SELECT a.Col1, b.Col2
FROM MyTable a
CROSS JOIN MyTable b

Upvotes: 20

Related Questions