How to reconfigure a value depending on two column values ​​in a statement to select SQL server?

Question

Please see below to understand what I want:

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

When I search for a value of 2 , I want the result of my search to return the associated values ​​in Friend and User , as shown below:

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

I need a column search result as shown below:

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

How to do this using a SELECT query in SQL Server?

Answer 1

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

Answer 2

 select case Friend when 2 then User else Friend end FROM table where User = 2 or Friend = 2; 

Answer 3

 Create Table #MyTable(id int identity,FriendID int,UserID int) Insert into #MyTable values(2,1),(3,2),(2,5),(4,2) Declare @SearchUserID int=2 select (Case Friendid when @SearchUserID then userID else FriendID end) as [Friends] from #MyTable where @SearchUserID in(Friendid,UserID) drop Table #MyTable 

Answer 4

Another option with a COALESCE expression

 SELECT COALESCE(CASE WHEN t.Friend = 2 THEN t.[User] END, CASE WHEN t.[User] = 2 THEN t.Friend END) AS [Friends of 2] FROM dbo.Friend t WHERE t.Friend = 2 OR t.[User] = 2 

See exmaple on SQLFiddle