Q:

How to get count of two-way combinations from two columns?

I work for a trucking company, and we're interested in getting a count of the number of times one of our trucks travels between two cities, in either direction.
I have a table that lists an origin and destination for each trip segment, such as:
Origin    Destination
City 1    City 2
City 2    City 1
City 3    City 4
City 2    City 1

I need a query that tells me that there were three trips between City 1 and City 2 and one trip between City 3 and City 4.  Thanks very much!

A:

I think the following should do the trick.  
SELECT route , COUNT(1) FROM 
(
   SELECT 
   CASE WHEN Origin > Destination THEN Origin+'_'+Destination
   ELSE Destination+'_'+Origin 
   END AS route
   FROM table1
 )a
 GROUP BY route

A:

Here is a way by sorting the columns, sort of. Granted, I'd change the column names from the output to something other than Origin and Destination since were are basically turning it into a "route". Perhaps just concat them like a1ex07 did. I left it normalized in case you wanted to alter it.
declare @table table (Origin varchar(16), Destination varchar(16))
insert into @table
values
('City 1','City 2'),
('City 2','City 1'),
('City 3','City 4'),
('City 2','City 1')

;with cte as(
select 
    case when Origin > Destination then Origin else Destination end as Origin
    ,case when Destination < Origin then Destination else Origin end as Destination
from
    @table)

select
    Origin
    ,Destination
    ,count(Origin + Destination) 
from 
    cte
group by
    Origin
    ,Destination

