title: Google Sheets Query to unpivot and fill null values
body: <p>I am writing a complex Google Sheets formula, which I think I can simplify using the Query function.  The Query formula takes two inputs: 1) data, 2) query.  The data consists of about 20 columns where the odd-numbered columns are categories and even-numbered columns are the values of that category.  (Note that there are a fixed number of rows, but various columns have variable number of non-blank entries.)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column A</th>
  <th>Column B</th>
  <th>Column C</th>
  <th>Column D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1st Category</td>
  <td>Value B1</td>
  <td>2nd Category</td>
  <td>Value D1</td>
  </tr>
  <tr>
  <td></td>
  <td>Value B2</td>
  <td></td>
  <td>Value D2</td>
  </tr>
  <tr>
  <td></td>
  <td>Value B3</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My dream output is as follows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Categories</th>
  <th>Values</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1st Category</td>
  <td>Value B1</td>
  </tr>
  <tr>
  <td>1st Category</td>
  <td>Value B2</td>
  </tr>
  <tr>
  <td>1st Category</td>
  <td>Value B3</td>
  </tr>
  <tr>
  <td>2nd Category</td>
  <td>Value D1</td>
  </tr>
  <tr>
  <td>2nd Category</td>
  <td>Value D2</td>
  </tr>
  <tr>
  <td>...etc.</td>
  <td>...etc.</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Thanks for any thoughts !</p>
  <p>Note: Common Table Expressions can't seem to be used in the Query function.</p>
  <p>Add'l note: I don't mind and partially expect that I will have to repeat the query for the transformation of Column A and Column B ten times to get all the data, which is fine.  The simpler first-step question is how to do that.</p>
  <p>Sample Google Sheets for reference/work: <a href="https://docs.google.com/spreadsheets/d/132E5CYwcv-ovWbZTnYqRMMqrR2X2pDv28Pj2bdW7w1k/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/132E5CYwcv-ovWbZTnYqRMMqrR2X2pDv28Pj2bdW7w1k/edit?usp=sharing</a></p>