title: Google sheets query: replacing a value in a cell by appending a list of values
body: <p>I have two sheets.</p>
  <p>One is an unpivoted sheet with groups and associated IDs(that is persons in this group) like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>group</th>
  <th>id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Group1</td>
  <td>ID1</td>
  </tr>
  <tr>
  <td>Group2</td>
  <td>ID2</td>
  </tr>
  <tr>
  <td>Group2</td>
  <td>ID4</td>
  </tr>
  <tr>
  <td>Group3</td>
  <td>ID1</td>
  </tr>
  <tr>
  <td>Group3</td>
  <td>ID2</td>
  </tr>
  <tr>
  <td>Group3</td>
  <td>ID3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The second is rooms and the group with access to that room:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>room1</th>
  <th>room2</th>
  <th>room3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Group1</td>
  <td>Group3</td>
  <td>Group1</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>Group2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to get an output like this using the google query in a new sheet. Where the groups have been replaced by their IDs:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>room1</th>
  <th>room2</th>
  <th>room3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ID1</td>
  <td>ID1</td>
  <td>ID1</td>
  </tr>
  <tr>
  <td></td>
  <td>ID2</td>
  <td>ID2</td>
  </tr>
  <tr>
  <td></td>
  <td>ID3</td>
  <td>ID4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My original dataset has over 1000 IDs, 100 groups, and 30 rooms.</p>