title: SQL : Concatenate data from a column depending on another column and reading row above
body: <p>I am writing you because I can't find any solution to my problem, and I don't even know if there is a way to do it. I'm working in SQL (MonetDB).</p>
  <p>I have a table like this :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>username</th>
  <th>Field1</th>
  <th>Field2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>AAA</td>
  <td>NULL</td>
  <td>1</td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type1</td>
  <td>12</td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type2</td>
  <td>21</td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>null</td>
  <td>6</td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>type1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>type4</td>
  <td>3</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type1</td>
  <td>23</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type3</td>
  <td>352</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type4</td>
  <td>12</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This table is already sorted by username (with nulls first), then field1, and i don't have any replicates on these two colums together.</p>
  <p>I need to update another column.
  When the row above have the same username and field1 isn't null, then new field3 should be the CONCAT(Field1, Field2)
  Then for the next row, if the username is the same but Field1 isn't null again and different from the previous one, then Field3 should be CONCAT(previous Field3, Field1, Field2).</p>
  <p>I'm trying to adapt a treatement who  do it, row by row, and retain the last value saved, that's why i'm struggling about doing it in a relational db.</p>
  <p>My final results should be :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>username</th>
  <th>Field1</th>
  <th>Field2</th>
  <th>Field 3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>AAA</td>
  <td>NULL</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type1</td>
  <td>12</td>
  <td>type1 : 12</td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type2</td>
  <td>21</td>
  <td>type1 : 12 type 2 : 21</td>
  </tr>
  <tr>
  <td>AAA</td>
  <td>type3</td>
  <td>1</td>
  <td>type1 : 12 type 2 : 21 type3: 1</td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>null</td>
  <td>6</td>
  <td></td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>type1</td>
  <td>2</td>
  <td>type1:2</td>
  </tr>
  <tr>
  <td>BBB</td>
  <td>type4</td>
  <td>3</td>
  <td>type1:2 type4:3</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type1</td>
  <td>23</td>
  <td>type1 : 23</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type3</td>
  <td>352</td>
  <td>type1 : 23 type 3:352</td>
  </tr>
  <tr>
  <td>CCC</td>
  <td>type4</td>
  <td>12</td>
  <td>type1 : 23 type 3:352 type 4:12</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The final goal is afterwards to select username and the longest field3 by username.
  Hope I've been clear in what I need to do, tell me if you need more explanations. Thank you for your help !</p>