SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tablename (scity varchar(20), dcity varchar(20), distance int);
insert into tablename (scity, dcity, distance) values
('City A', 'City B', 30),
('City A', 'City B', 32),
('City B', 'City A', 29),
('City A', 'City C', 40),
('City C', 'City A', 41)
5 rows affected

select 
  (case when scity < dcity then scity else dcity end) city1, 
  (case when scity < dcity then dcity else scity end) city2,
  avg(cast(distance as float)) distance
from tablename 
group by
  (case when scity < dcity then scity else dcity end), 
  (case when scity < dcity then dcity else scity end)
order by avg(cast(distance as float)) desc


city1
city2
distance


City A
City C
40.5

City A
City B
30.3333333333333


select 
  (case when scity < dcity then scity else dcity end) city1, 
  (case when scity < dcity then dcity else scity end) city2,
  (cast(distance as float)) distance
from tablename


city1
city2
distance


City A
City B
30

City A
City B
32

City A
City B
29

City A
City C
40

City A
City C
41

city1	city2	distance
City A	City B	30
City A	City B	32
City A	City B	29
City A	City C	40
City A	City C	41