Merging two rows into single row

Try the below:

Drop table T11,T12
Create Table T11(Col1 int,Col2 int)
Insert into T11 Values (100,101),(1234,1235)
Create Table T12(S1 int,S2 int)
Insert into T12 
Values 
(100,500),
(100,400),
(101,200),
(101,300),
(1234,500),
(1234,400),
(1235,200),
(1235,700)
Create Table #Result(Col1 int,Col2 int)
;With cte
AS
(
        Select *,ROW_NUMBER()Over(partition by A.Col1 Order by B.S2 desc) Rn From T11 A
        Inner Join T12 B on (A.Col1 = B.S1 OR A.Col2 = B.S1)
)Insert Into #Result(Col1,Col2)
Select Case when S1 = Col1 Then Col1 Else Col2 End, 
                        S2
From cte Where Rn=1

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.