× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

How to Pivot in Google BigQuery

Marton
Jun 12, 2015
<p>This is a way to do:</p> <pre><code>select shipmentID, sum(IF (category='shoes', quantity, 0)) AS shoes, sum(IF (category='hats', quantity, 0)) AS hats, sum(IF (category='shirts', quantity, 0)) AS shirts, sum(IF (category='toys', quantity, 0)) AS toys, sum(IF (category='books', quantity, 0)) AS books, from (select 1 as shipmentID, 'shoes' as category, 5 as quantity), (select 1 as shipmentID, 'hats' as category, 3 as quantity), (select 2 as shipmentID, 'shirts' as category, 1 as quantity), (select 2 as shipmentID, 'hats' as category, 2 as quantity), (select 3 as shipmentID, 'toys' as category, 3 as quantity), (select 2 as shipmentID, 'books' as category, 1 as quantity), (select 3 as shipmentID, 'shirts' as category, 1 as quantity), group by shipmentID </code></pre> <p>This returns:</p> <pre><code>+-----+------------+-------+------+--------+------+-------+---+ | Row | shipmentID | shoes | hats | shirts | toys | books | | +-----+------------+-------+------+--------+------+-------+---+ | 1 | 1 | 5 | 3 | 0 | 0 | 0 | | | 2 | 2 | 0 | 2 | 1 | 0 | 1 | | | 3 | 3 | 0 | 0 | 1 | 3 | 0 | | +-----+------------+-------+------+--------+------+-------+---+ </code></pre> <p>See the manual for other <a href="https://cloud.google.com/bigquery/query-reference" rel="nofollow">pivot table example</a>.</p> <p>This tip was originally posted on <a href="http://stackoverflow.com/questions/30715007/How%20to%20Pivot%20in%20Google%20BigQuery/30716063">Stack Overflow</a>.</p>
comments powered by Disqus