Combine two rows in one table to single row using SQL

Question

ID  Name    Address Acc_Number  BookingNo   Amt
 1  MERY    Kollam  111111111   NULL        1000
 2  Jos     Kochi   111111111   c/01        NULL

output

ID  Name    Address Acc_Number  BookingNo   Amt
1   MERY    Kollam  111111111   c/01        1000

Answer 1

I think self-joining to the same table using the Acc_Number should achieve that

SELECT 
     a.ID,  
     a.Name,    
     a.Address, 
     a.Acc_Number,  
     COALESCE(a.BookingNo, b.BookingNo) as BookingNo,   
     COALESCE(a.Amt, b.Amt) As Amt
FROM TableA a
JOIN TableA b
     ON a.Acc_Number = b.Acc_Number

Answer 2

SELECT top 1 t1.ID,t1.Name,  
     t1.Address, 
     t1.Acc_Number,  
     COALESCE(t1.BookingNo, t2.BookingNo) as 'BookingNo',COALESCE(t1.Amt, t2.Amt) as 'Amt'
FROM testtable1 t1 JOIN testtable1 t2 
     ON t1.Acc_Number = t2.Acc_Number and t1.NAME!=t2.NAME