title: How to Pivot table in BigQuery
body: <p>I am using Google Big Query, and I am trying to get a pivoted result out from public sample data set.</p>
  
  <p>A simple query to an existing table is:</p>
  
  <pre><code>SELECT * 
  FROM publicdata:samples.shakespeare
  LIMIT 10;
  </code></pre>
  
  <p>This query returns following result set.</p>
  
  <p><img src="https://i.stack.imgur.com/Oj73n.png" alt="enter image description here"></p>
  
  <p>Now what I am trying to do is, get the results from the table in such way that if the word is brave, select "BRAVE" as column_1 and if the word is attended, select "ATTENDED" as column_2, and aggregate the word count for these 2.</p>
  
  <p>Here is the query that I am using.</p>
  
  <pre><code>SELECT
  (CASE WHEN word = 'brave' THEN 'BRAVE' ELSE '' END) AS column_1,
  (CASE WHEN word = 'attended' THEN 'ATTENDED' ELSE '' END) AS column_2,
  SUM (word_count)
  FROM publicdata:samples.shakespeare
  WHERE (word = 'brave' OR word = 'attended')
  GROUP BY column_1, column_2
  LIMIT 10;
  </code></pre>
  
  <p>But, this query returns the data</p>
  
  <p><img src="https://i.stack.imgur.com/y4XB5.png" alt="enter image description here"></p>
  
  <p>What I was looking for is</p>
  
  <p><img src="https://i.stack.imgur.com/gaM5T.png" alt="enter image description here"></p>
  
  <p>I know this pivot for this data set does not make sense. But I am just taking this as an example to explain the problem. It will be great if you can put in some directions for me.</p>
  
  <p>EDITED: I also referred to <a href="https://stackoverflow.com/questions/19414609/how-to-simulate-a-pivot-table-with-bigquery">How to simulate a pivot table with BigQuery?</a> and it seems it also has the same issue I mentioned here.</p>