To get all of the combinations from origins to destinations first get a list of all possible origins and destinations by doing a
UNION[
^] selecting first the Origin from the table and then the Destination. You might (should) already have a table of the location points so you could use that.
Store that data in a temporary table, a table variable or use a
CTE[
^]
You can then use a
CROSS JOIN[
^] on that data to get all combinations. In this instance don't forget to filter out
WHERE t1.Location<> t2.Location
You can give each destination for each origin a number using
ROW_NUMBER[
^] which you could then use to filter as you describe.
Example - If I have something that contains all locations I could do something like this
select
a1.Origin
,a2.Origin as Destination
,ROW_NUMBER() OVER (partition by a1.Origin order by a1.Origin,a2.Origin) as rn
from allLocations a1
cross join allLocations a2
where a1.Origin <> a2.Origin
which would give me a result set similar to
Origin Destination rn
A B 1
A C 2
A D 3
A E 4
B A 1
B C 2
B D 3
B E 4
C A 1
...etc