title: WHILE loop to generate columns using Google BigQuery SQL
body: <p>I'm trying to query a table and generate in my output a number of N different columns. The 'N' depends on the number of months between a certain date and today.</p>
  <p>This is what I'm trying:</p>
  <pre><code>DECLARE mes DATE DEFAULT '2021-11-01';
  DECLARE mes_1 DATE;
  
  SELECT 
      issue_date,
      cancell_date,
      WHILE mes &lt;= CURRENT_DATE DO
        SET mes_1 = DATE_ADD(mes, INTERVAL 1 MONTH)
        CASE WHEN issue_date &gt;= mes_1 THEN 0
        WHEN issue_date &lt; mes AND cancell_date &lt; mes THEN 0
        WHEN issue_date &lt; mes AND cancell_date IS NULL THEN 1
        WHEN issue_date &lt; mes AND cancell_date &gt;= mes_1 THEN 1
        WHEN issue_date &lt; mes AND cancell_date &gt;= mes AND cancell_date &lt; mes_1 THEN DATE_DIFF(cancell_date, mes, DAY)/30
        WHEN issue_date&lt; mes_1 AND issue_date &gt;= mes AND cancell_date IS NULL THEN DATE_DIFF(mes_1,issue_date, DAY)/30
        WHEN issue_date&lt; mes_1 AND issue_date &gt;= mes AND cancell_date &gt;= mes_1 THEN DATE_DIFF(mes_1,issue_date, DAY)/30
        WHEN issue_date &lt; mes_1 AND issue_date &gt;= mes AND cancell_date &lt; mes_1 THEN DATE_DIFF(cancell_date, issue_date, DAY)/30
        ELSE 0 END AS CONCAT(&quot;exposicion_&quot;,EXTRACT(YEAR FROM mes),&quot;_&quot;,EXTRACT(MONTH FROM mes),&quot;_&quot;,EXTRACT(DAY FROM mes))
        SET mes = DATE_ADD(mes, INTERVAL 1 MONTH)
      END WHILE
  FROM my_table;
  </code></pre>
  <p>The input looks like this:</p>
  <pre><code>issue_date | cancell_date|
  10/15/2021 |             |
  10/15/2021 |  11/10/2021 |
  11/11/2021 |  12/29/2021 |
  12/01/2021 |             |
  </code></pre>
  <p>And setting <code>mes = 11/1/2021</code> and <code>current_date = 12/31/2021</code> the output should be this:</p>
  <pre><code>issue_date | cancell_date|exposicion_2021_11_01|exposicion_2021_12_01|
  10/15/2021 |             |                    1|                    1|
  10/15/2021 |  11/10/2021 |               0.3333|                    0|
  11/11/2021 |  12/29/2021 |              0.63333|              0.96667|
  12/01/2021 |             |                    0|                    1|
  </code></pre>
  <p>That's what I'm trying to do but I'm failing on it. I asssume that this isn't the way to approach this problem, I've been doing some research but can't find the solution.</p>
  <p>I appreciate someone can give me at least a hint of how to approach this.</p>
  <p>Thanks!</p>