title: In BigQuery, create ARRAY of STRUCTs from longer data when grouping-by
body: <p>We have a table of player info, with unique rows for each name-year-team:</p>
  <pre><code>with
    player_info as (
      select 'joe' as name, 1950 as year, 'bills' as team union all
      select 'joe' as name, 1950 as year, 'jazz' as team union all
      select 'joe' as name, 1951 as year, 'jazz' as team union all
      select 'joe' as name, 1952 as year, 'rafs' as team union all
      select 'nick' as name, 1950 as year, 'bills' as team union all
      select 'nick' as name, 1951 as year, 'bills' as team union all
      select 'chris' as name, 1950 as year, 'jazz' as team union all
      select 'chris' as name, 1951 as year, 'jazz' as team union all
      select 'sal' as name, 1950 as year, 'dopers' as team
    )
    
  select * from player_info
  </code></pre>
  <p><a href="https://i.stack.imgur.com/SCKpRm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SCKpRm.png" alt="enter image description here" /></a></p>
  <p>We need a unique row for each <strong>player</strong>, with a struct containing the year-team informations:</p>
  <pre><code>SELECT 'joe' AS name, [
    struct(1950 as year, 'bills' as team),  
    struct(1950 as year, 'jazz' as team),
    struct(1951 as year, 'jazz' as team),  
    struct(1952 as year, 'rafs' as team)
  ] as team_years
  
  union all
  
  SELECT 'nick' as name, [
      struct(1950 as year, 'bills' as team),  
      struct(1951 as year, 'bills' as team)
  ] as team_years
  
  union all 
  
  SELECT 'chris' as name, [
      struct(1950 as year, 'jazz' as team),  
      struct(1951 as year, 'jazz' as team)
  ] as team_years
  
  union all
  
  select 'sal' as name, [
      struct(1950 as year, 'dopers' as team)
  ] as team_years
  </code></pre>
  <p><a href="https://i.stack.imgur.com/kgoY9m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kgoY9m.png" alt="enter image description here" /></a></p>
  <p>How can we do this in BigQuery?</p>