CODEWITHSUNDEEP
sqlsql-servert-sqlpermutation
user3670015
user3670015

Reputation: 21

Get all permutations of values - into pairs

I have X amount of values being passed into a table via CSV - so I take 99315,99316,99223 and split them out into a single column temp table - each value in the CSV into a single row.

What I need to be able to do is to get every permutation of values in pairs - so - something like:

Col1     Col2
99315    99316
99315    99223
99316    99315
99316    99223
99223    99315
99223    99316

Upvotes: 2

Views: 205

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

A faster way to write this query is to just do

SELECT t1.col1, t2.col1 col2
FROM table1 t1, table2 t2

and then you can add filters with a WHERE as well

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

select t1.col1, t2.col1 col2
from mytable t1
cross join mytable t2

if you want to exclude like values add

where t1.col1 <> t2.col1

Upvotes: 5

Related Questions