title: Extract and concatenate the same field from multiple records in big query
body: <p>I would like to be able to extract one field from multiple records from within a single table.  For example, assuming I have a schema as follows</p>
  
  <p>userId, eventTimestamp, theField</p>
  
  <p>And what I want to do is be able to concatenate all instances of the field 'theField' together into a single string for a given userId ordered by eventTimestamp.  And for an extra wrinkle, lets say I only want to include the first fiftiest oldest records.</p>
  
  <p>My first attempt was to try something like:</p>
  
  <pre><code> SELECT
   userId,
   eventTimestamp,
   LEAD(theField,0) OVER (PARTITION BY userId ORDER BY eventTimestamp) AS step0,
   LEAD(theField,1) OVER (PARTITION BY userId ORDER BY eventTimestamp) AS step1,
   ....,
   LEAD(theField,50) OVER (PARTITION BY userId ORDER BY eventTimestamp) AS step50,
  </code></pre>
  
  <p>And then the next step was to wrap that first step up in another SELECT statement as follows:</p>
  
  <pre><code> SELECT userId, eventTimestamp, CONCAT(STRING(step0), STRING(step1),...,STRING(step50)) as concatenatedString
   FROM [whateverDataset.whateverTable],
   GROUP BY
   userId, eventTimestamp
  </code></pre>
  
  <p>This approach doesn't work though because if I have more than 50 steps (which I do), then I end up getting multiple rows for each of those outer SELECT statements, basically N-50 rows, where N = the total number of records for a particular userId. A 'solution' to this would be to have a HAVING statement in the inner SELECT statement to limit itself to only reporting the first 50 records, but overall this seems like a rather cumbersome solution.  In non-BigQuery variants of SQL the GROUP_CONCAT seems to be a good way to go forward, but it either doesn't work here or I lack the creativity to get it to work.  Anyone have any suggestions?</p>
  
  <p>Thanks,
  Brad</p>