title: How to merge multiple rows with same column value into one row in sql db2 and join with another table
body: <p>I have this table and I want to combine rows that have the same REFNO into one row</p>
  <p><a href="https://i.stack.imgur.com/3Vrz3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3Vrz3.png" alt="enter image description here" /></a></p>
  <p>This is the result that I want, the ID follows the upper row so if I merge row 2 and 3 the new row will have an id of 2</p>
  <p><a href="https://i.stack.imgur.com/kN1LP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kN1LP.png" alt="enter image description here" /></a></p>
  <p>I want to join this table with another table according to its REFNO. This is the other table</p>
  <p><a href="https://i.stack.imgur.com/xfpjt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xfpjt.png" alt="enter image description here" /></a></p>