title: SQL to select unique combination of two columns having interchangeable values
body: <p>I have a flights table that has columns like &quot;DEP&quot; (departure) and &quot;ARR&quot; (arrival).</p>
  <p>It is obvious that Departure and arrival columns can have interchangeable city names. For example, 1 row can have &quot;SEA&quot; as departure and &quot;NYC&quot; as arrival while another row can have &quot;NYC&quot; as departure and &quot;SEA&quot; as arrival.</p>
  <p>I want to fetch only the unique combinations of departure and arrival cities. So, in this case, I want to see either of the 2 rows as output but not both the rows.</p>
  <p>Here is how my table looks more realistically with data:</p>
  <pre><code>CREATE TABLE flights(DEP CHAR(3), ARR CHAR(3), COST INT)
  
  INSERT INTO flights VALUES ('SEA', 'CHG', 100)
  INSERT INTO flights VALUES ('CHG', 'SEA', 100)
  INSERT INTO flights VALUES ('SEA', 'SFO', 100)
  INSERT INTO flights VALUES ('SEA', 'LSA', 100)
  INSERT INTO flights VALUES ('SEA', 'SJO', 100)
  INSERT INTO flights VALUES ('SFO', 'CHG', 100)
  INSERT INTO flights VALUES ('SFO', 'SEA', 100)
  INSERT INTO flights VALUES ('BOS', 'SEA', 100)
  INSERT INTO flights VALUES ('NYC', 'CHG', 100)
  INSERT INTO flights VALUES ('NYC', 'SEA', 100)
  INSERT INTO flights VALUES ('SEA', 'NYC', 100)
  
  SELECT * FROM flights --(11 rows)
  
  DEP  ARR  COST
  ---- ---- ----
  BOS  SEA  100
  CHG  SEA  100
  NYC  CHG  100
  NYC  SEA  100
  SEA  CHG  100
  SEA  LSA  100
  SEA  NYC  100
  SEA  SFO  100
  SEA  SJO  100
  SFO  CHG  100
  SFO  SEA  100
  </code></pre>
  <p>For above table my output should be like (8 rows):</p>
  <pre><code>COL1 COL2 
  ---- ----
  SFO  SEA
  SFO  CHG
  SEA  SJO
  SEA  NYC
  SEA  LSA
  SEA  CHG
  NYC  CHG
  BOS  SEA
  </code></pre>
  <p>I was able to write a TSQL code using temp tables, variables, and loop etc but I strongly feel that this can be achieved in more simpler SQL probably by using UNION/INTERSECT/EXCEPT/EXISTS etc.</p>
  <p>Here is my solution:</p>
  <pre><code>DECLARE @i INT = 1  --loop counter
  DECLARE @exist BIT = 0  --flag to check if the combination  already exists
  DECLARE @dep CHAR(3), @arr CHAR(3)
  
  SELECT @i = COUNT(*) FROM dbo.flights   --get the row count
  
  CREATE TABLE #tResult(dep CHAR(3), arr CHAR(3)) --output table
  CREATE TABLE #tTemp (id TINYINT identity, dep CHAR(3), arr CHAR(3))
  
  INSERT INTO #tTemp (dep, arr)
  SELECT DISTINCT dep, arr FROM flights
  
  
  WHILE (@i &gt; 0)
  BEGIN
      
      --get 
      SELECT @dep = dep, @arr = arr
      FROM #tTemp WHERE id = @i
  
      SET @exist = (SELECT count(*) FROM #tResult WHERE arr = @dep AND dep = @arr)    --check if this combination has been inserted in output
  
      --if not exists, insert this combination
      IF (@exist = 0)
      BEGIN
          INSERT INTO #tResult (dep, arr)
          VALUES (@dep, @arr)
      END
  
      SET @i = @i - 1 --decrement loop counter
  END
  
  SELECT * FROM #tResult
  </code></pre>
  <p>Can anyone please share a better or simpler solution to this problem.
  Thanks!</p>