Select pairs (a,b) excluding (b,a) with same value For a given structure like this:

+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+


Get the list of pairs (ID, ID2) with same Name, excluding all records where (ID2, ID) has the corresponding (ID, ID2).

+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+


I've found this solution, but I wonder if there is a more "academic" solution.

;WITH Pair AS
(
SELECT
    CASE WHEN p1.Id &gt; p2.Id THEN p2.Id ELSE p1.Id END as ID,
    CASE WHEN p1.Id &gt; p2.Id THEN p1.Id ELSE p2.Id END as ID2,
    p1.Name
FROM Pairs p1
     LEFT JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id &lt;&gt; p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;


Rextester

CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');
SELECT
    p1.Id,p2.Id,p1.Name 
FROM Pairs p1
     JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id &lt; p2.Id