title: BigQuery ARRAY_AGG(STRUCT) splitting values basing on column value
body: <p>I have a BigQuery table like this:</p>
  
  <pre><code>+------+------------+----------+-------+--------+
  | Name |    Date    | Category | Value | Number |
  +------+------------+----------+-------+--------+
  | John | 2019-01-03 | Cat1     | AA    |     10 |
  | John | 2019-01-03 | Cat1     | AB    |     11 |
  | John | 2019-01-03 | Cat2     | NN    |     12 |
  | John | 2019-01-03 | Cat2     | MM    |     13 |
  +------+------------+----------+-------+--------+
  </code></pre>
  
  <p>The first 2 columns are the key identifier and I need to ARRAY/GROUP the rows basing on those 2 columns.</p>
  
  <p>Here is the sample statement:</p>
  
  <pre><code>WITH data AS (
    SELECT "John" name, DATE("2019-01-03") date, "cat1" category, "AA" value, 10 number
    UNION ALL
    SELECT "John", DATE("2019-01-03"), "cat1", "AB", 11
    UNION ALL
    SELECT "John", DATE("2019-01-03"), "cat2", "NN", 12
    UNION ALL
    SELECT "John", DATE("2019-01-03"), "cat2", "MM", 13
  )
  
  SELECT * FROM data
  </code></pre>
  
  <p>The basic version of the query is very simple:</p>
  
  <pre><code>SELECT 
    name,
    date,
    ARRAY_AGG(
      STRUCT&lt;category STRING, value STRING, number INT64&gt;(category,value,number)
    ) AS items
  
  FROM data
  GROUP BY 1,2
  </code></pre>
  
  <p>but in my case I need to distinct the values (on 2 different columns) the <code>value</code>-<code>number</code> grouped values based on <code>category</code> column</p>
  
  <p>I don't know if a dynamic column definition can be made, basing on the DISTINCT values of the <code>category</code> values, but in a simplier case I can use fixed values <code>cat1</code> and <code>cat2</code></p>
  
  <p>Here an example of the output I described:</p>
  
  <pre><code>+------+------------+--------------------+---------------------+--------------------+---------------------+
  | Name |    Date    | cat1_grouped.value | cat1_grouped.number | cat2_grouped.value | cat2_grouped.number |
  +------+------------+--------------------+---------------------+--------------------+---------------------+
  | John | 2019-01-03 | AA                 |                  10 | NN                 |                  12 |
  |      |            | AB                 |                  11 | MM                 |                  13 |
  |      |            |                    |                     |                    |                     |
  +------+------------+--------------------+---------------------+--------------------+---------------------+
  </code></pre>