title: Transpose nested rows into columns in bigquery with google analytics data
body: <p>I am interested in pulling visitors with custom dimension attributes, where each row is a unique fullvisitorid and columns are desired customdimension.values.</p>
  
  <p>Using the london helmets as an example, here I am pulling visitors with the two custom dimensions I am interested in:</p>
  
  <pre><code>SELECT fullvisitorid, customDimensions.index, customDimensions.value
  FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
  where customDimensions.index in (2,3)
  group by fullvisitorid, customDimensions.index, customDimensions.value
  </code></pre>
  
  <p>It gives results like:</p>
  
  <pre><code>+---------------+------------------------+------------------------+
  | fullvisitorid | customDimensions_index | customDimensions_value |
  +---------------+------------------------+------------------------+
  |             1 |                      2 | Bronze                 |
  |             1 |                      3 | Yes                    |
  |             2 |                      2 | Bronze                 |
  |             2 |                      3 | No                     |
  |             3 |                      2 | Bronze                 |
  |             3 |                      3 | Yes                    |
  |             4 |                      2 | Platinum               |
  |             4 |                      3 | Yes                    |
  +---------------+------------------------+------------------------+
  </code></pre>
  
  <p>I would like the values transposed, where customDimension_index 2 is color, and customDimension_value 3 is yesno, so results would look like this instead:</p>
  
  <pre><code>+---------------+----------+-------+
  | fullvisitorid |  color   | yesno |
  +---------------+----------+-------+
  |             1 | Bronze   | Yes   |
  |             2 | Bronze   | No    |
  |             3 | Bronze   | Yes   |
  |             4 | Platinum | Yes   |
  +---------------+----------+-------+
  </code></pre>
  
  <p>I could pull one then the other separately and join on fullvisitorid, but hoping to be able to pull out data this way in a single step. Thanks!</p>