Return all possible combinations of values on columns in SQL

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

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

Tags:

Sql Server

Tsql

Related