Merge a two way relation in the same table in SQL Server

Question

Current Data

ID      | Name1      | Name2
<guid1> | XMind      | MindNode    
<guid2> | MindNode   | XMind         
<guid3> | avast      | Hitman Pro    
<guid4> | Hitman Pro | avast
<guid5> | PPLive     | Hola!    
<guid6> | ZenMate    | Hola!    
<guid7> | Hola!      | PPLive
<guid8> | Hola!      | ZenMate  

Required Output

ID1       | ID2       | Name1  | Name2
<guid1>   | <guid2>   | XMind  | MindNode 
<guid3>   | <guid4>   | avast  | Hitman Pro  
<guid5>   | <guid7>   | PPLive | Hola!
<guid6>   | <guid8>   | Hola!  | ZenMate         

These are relations between apps. I want to show that Avast and Hitman has a relation but in this view i do not need to show in what "direction" they have an relation. It's a given in this view that the relation goes both ways.

EDIT: Seems like my example was to simple. The solution doesn't work with more data.

DECLARE @a TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'Hitman Pro', 'avast' )
INSERT  INTO @a VALUES  ( 5, 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( 6, 'ZenMate', 'Hola! Better Internet' )       
INSERT  INTO @a VALUES  ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( 8, 'Hola! Better Internet', 'ZenMate' )

SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2
FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
        AND a1.ID < a2.ID -- avoid duplicates

This works however so i guess it's the Guid that is messing with me.

EDIT AGAIN:

I haven't looked at this for a while and i thought it worked but i just realized it does not. I've struggled all morning with this but i must admit that SQL is not really my strong suite. The thing is this.

DECLARE @a TABLE (ID int, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( 5, 'ZenMate', 'Hola! Better Internet' )
INSERT  INTO @a VALUES  ( 6, 'Hitman Pro', 'avast' )       
INSERT  INTO @a VALUES  ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( 8, 'Hola! Better Internet', 'ZenMate' )
INSERT  INTO @a VALUES  ( 9, 'XX', 'A' )  
INSERT  INTO @a VALUES  ( 10, 'XX', 'BB' )  
INSERT  INTO @a VALUES  ( 11, 'BB', 'XX' )
INSERT  INTO @a VALUES  ( 12, 'A', 'XX' )
INSERT  INTO @a VALUES  ( 13, 'XX', 'CC' )  
INSERT  INTO @a VALUES  ( 14, 'CC', 'XX' )

;With CTE as
(
SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2,
        CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, -- just for display
        Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)
                           order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn

FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
)
Select ID1, ID2,Name1, Name2
from CTE C1
where rn=1

When i use this code it sure works fine with the names but it doesn't match the ID's correctly.

The result is

ID1       | ID2       | Name1  | Name2
12        | 9         | A      | X (Correct)
7         | 5         | Hola!  | ZenMate (Not Correct)   
[..]

I've pulled my hair all morning but i can't figure this out. I still use Guid's as ID's and just use Int's here to make it a bit more readable.

Answer 1

DECLARE @a TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( 1, 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( 2, 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( 3, 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( 4, 'Hitman Pro', 'avast' )

SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2
FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
        AND a1.ID < a2.ID -- avoid duplicates

Referring to the amendment and extension of your question, a more complicated solution is required. We form a CHECKSUM on a1.Name1,a2.Name (to get an identical we exchanged on size).
Using this we generate with ROW_NUMBER (Transact-SQL) a number and use only rows from the result with number 1.

DECLARE @a TABLE (ID uniqueIdentifier, Name1 VARCHAR(50), Name2 VARCHAR(50))
INSERT  INTO @a VALUES  ( NewID(), 'XMind', 'MindNode' )    
INSERT  INTO @a VALUES  ( NewID(), 'MindNode', 'XMind' )       
INSERT  INTO @a VALUES  ( NewID(), 'avast', 'Hitman Pro' )  
INSERT  INTO @a VALUES  ( NewID(), 'Hitman Pro', 'avast' )
INSERT  INTO @a VALUES  ( NewID(), 'PPLive Video Accelerator', 'Hola! Better Internet' )    
INSERT  INTO @a VALUES  ( NewID(), 'ZenMate', 'Hola! Better Internet' )       
INSERT  INTO @a VALUES  ( NewID(), 'Hola! Better Internet', 'PPLive Video Accelerator' )  
INSERT  INTO @a VALUES  ( NewID(), 'Hola! Better Internet', 'ZenMate' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'A' )  
INSERT  INTO @a VALUES  ( NewID(), 'A', 'XX' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'BB' )  
INSERT  INTO @a VALUES  ( NewID(), 'BB', 'XX' )
INSERT  INTO @a VALUES  ( NewID(), 'XX', 'CC' )  
INSERT  INTO @a VALUES  ( NewID(), 'CC', 'XX' )


;With CTE as
(
SELECT  a1.ID AS ID1 ,
        a2.ID AS ID2 ,
        a1.Name1 ,
        a2.Name1 AS Name2,
        CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, -- just for display
        Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)
                           order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn

FROM    @a a1
JOIN @a a2 ON a1.Name1 = a2.Name2
)
Select *
from CTE C1
where rn=1

Edit:
If you only want to get those where both fields are fitting the needed query would simply be:

SELECT a1.ID AS ID1 , a2.ID AS ID2 , a1.Name1 , a2.Name1 AS Name2 
FROM @a a1 
JOIN @a a2 ON a1.Name1 = a2.Name2 and a1.Name2 = a2.Name1 AND a1.ID < a2.ID

Answer 2

If the output should contain only two-way relations ('XX' + 'A') AND ('A' + 'XX'), try this:

;
WITH m    (ID1, ID2, Name1, Name2) AS (
    SELECT ID1, ID2, Name1, Name2
    FROM (
        SELECT a1.ID AS ID1
              ,a2.ID AS ID2
              ,a1.Name1 AS Name1
              ,a2.Name1 AS Name2
              ,ROW_NUMBER() OVER (PARTITION BY a1.Name1, a2.Name1 ORDER BY (SELECT 1)) AS n
        FROM @a AS a1
        JOIN @a AS a2
            ON a1.Name1 = a2.Name2
                AND a1.Name2 = a2.Name1
    ) AS T
    WHERE n = 1
)
SELECT DISTINCT *
FROM (
    SELECT ID1, ID2, Name1, Name2
    FROM m
    WHERE ID1 <= ID2

    UNION ALL

    SELECT ID2, ID1, Name2, Name1
    FROM m
    WHERE ID1 > ID2
) AS dm

It produces the output as follows:

+------+-----+--------------------------+-----------------------+
| ID1  | ID2 | Name1                    | Name2                 |
+------+-----+--------------------------+-----------------------+
|    1 |   2 | XMind                    | MindNode              |
|    3 |   6 | avast                    | Hitman Pro            |
|    4 |   7 | PPLive Video Accelerator | Hola! Better Internet |
|    5 |   8 | ZenMate                  | Hola! Better Internet |
|    9 |  12 | XX                       | A                     |
|   10 |  11 | XX                       | BB                    |
|   13 |  14 | XX                       | CC                    |
+------+-----+--------------------------+-----------------------+