title: Pivot data in BigQuery SQL?
body: <p>I am working with BigQuery. I have two tables:</p>
  
  <pre><code>organisations:
    org_code STRING
    name STRING
  
  spending:
    org STRING
    month DATE
    quantity INTEGER
    code STRING
  </code></pre>
  
  <p>And then quite a complicated query to get results by each organisation, by month:</p>
  
  <pre><code>SELECT
    organisations.org_code AS org,
    num.month AS month,
    (num.quantity / denom.quantity) AS ratio_quantity
  FROM (
    SELECT
      org_code, name
    FROM
      [mytable.organisations]) AS organisations
  LEFT OUTER JOIN EACH (
    SELECT
      org,
      month,
      SUM(quantity) AS quantity
    FROM
      [mytable.spending]
    GROUP BY
      org,
      month) AS denom
  ON
    denom.org = organisations.org_code
  LEFT OUTER JOIN EACH (
    SELECT
      org,
      month,
      SUM(quantity) AS quantity
    FROM
      [hscic.spending]
    WHERE
      code LIKE 'XXXX%'
    GROUP BY
      org,
      month) AS num
  ON
    denom.month = num.month
    AND denom.org = num.org
  ORDER BY org, month
  </code></pre>
  
  <p>My final results look like this, with a row per org/month combination:</p>
  
  <pre><code>org,month,ratio_quantity
  A81001,2015-10-01 00:00:00 UTC,28
  A82001,2015-11-01 00:00:00 UTC,43
  A82002,2015-10-01 00:00:00 UTC,16
  </code></pre>
  
  <p>Now I would like to pivot the results to look like this, with one row per month, and one column per organisation:</p>
  
  <pre><code>month,items.A81001,items.A82002...
  2015-10-01 00:00:00 UTC,28,16
  2015-11-01 00:00:00 UTC,43,...
  </code></pre>
  
  <p>Is this possible in the same BigQuery call? Or should I create a new table and pivot it from there? Or should I just do the reshaping in Python?</p>
  
  <p>UPDATE: There are about 500,000 results, fyi.  </p>