How to retun a value depending on Two column values in SQL server select statement?

Question

Please see below to understand what I want:

------------------------------
   Friend       | User 
------------------------------
     2          |  1 
     3          |  2 
     2          |  5
     4          |  2 
------------------------------

When I search for value 2, I want my search result to return related values in Friend and User, like below:

--------------------------
      Friends of 2 
--------------------------
        1   - (from column User ) 
        3   - (from column Friend) 
        5   - (from column user ) 
        4   = (from column friend )  

I want one column search result like below:

--------------------------
      Friends of 2 
--------------------------
        1  
        3   
        5   
        4   

How can I accomplish this by using a SELECT query in SQL Server?

Answer 1

SELECT Friend FROM MyTable WHERE User = 2
UNION
SELECT User FROM MyTable WHERE Friend = 2;