Cómo pivotar en Google BigQuery

Supongamos que tengo la siguiente consulta enviada a BQ:

SELECT shipmentID, category, quantity FROM [myDataset.myTable] 

Además, supongamos que la consulta devuelve datos como:

 shipmentID category quantity 1 shoes 5 1 hats 3 2 shirts 1 2 hats 2 3 toys 3 2 books 1 3 shirts 1 

¿Cómo puedo hacer pivotar los resultados, desde dentro de BQ, para producir resultados de la siguiente manera:

  shipmentID shoes hats shirts toys books 1 5 3 0 0 0 2 0 2 1 0 1 3 0 0 1 3 0 

Como fondo adicional, en realidad tengo más de 2000 categorías que necesito pivotar, y la cantidad de datos es tal que no puedo hacerlo directamente a través de un Pandas DataFrame en Python (usa toda la memoria, luego disminuye la velocidad de rastreo) . Intenté usar una base de datos relacional, pero me topé con un límite de columna, así que me gustaría poder hacerlo directamente en BQ, incluso si tengo que crear la consulta a través de Python. ¿Alguna sugerencia?

** Edición 1 Debo mencionar que la rotación de los datos en sí se puede hacer en trozos y por lo tanto no es el problema. El problema real viene al intentar hacer la agregación después, de modo que solo tengo una fila para cada ID de envío. Eso es lo que se come toda la memoria RAM.

** Editar 2 Después de probar la respuesta aceptada a continuación, descubrí que tratar de usarla para crear una tabla dinámica de 2k + columna estaba causando errores de “Recursos excedidos”. Mi equipo de BQ pudo refactorizar la consulta para dividirla en partes más pequeñas y permitir que se realice. La estructura básica de la consulta es la siguiente:

 SELECT SetA.*, SetB.*, SetC.* FROM ( SELECT shipmentID, SUM(IF (category="Rocks", qty, 0)), SUM(IF (category="Paper", qty, 0)), SUM(IF (category="Scissors", qty, 0)) FROM ( SELECT a.shipmentid shipmentid, a.quantity quantity, a.category category FROM [myDataset.myTable] a) GROUP EACH BY shipmentID ) SetA INNER JOIN EACH ( SELECT shipmentID, SUM(IF (category="Jello Molds", quantity, 0)), SUM(IF (category="Torque Wrenches", quantity, 0)) FROM ( SELECT a.shipmentID shipmentID, a.quantity quantity, a.category category FROM [myDataset.myTable] a) GROUP EACH BY shipmentID ) SetB ON SetA.shipmentid = SetB.shipmentid INNER JOIN EACH ( SELECT shipmentID, SUM(IF (category="Deep Thoughts", qty, 0)), SUM(IF (category="Rainbows", qty, 0)), SUM(IF (category="Ponies", qty, 0)) FROM ( SELECT a.shipmentid shipmentid, a.quantity quantity, a.category category FROM [myDataset.myTable] a) GROUP EACH BY shipmentID ) SetC ON SetB.shipmentID = SetC.shipmentID 

El patrón anterior se puede continuar indefinidamente agregando segmentos INNER JOIN EACH uno tras otro. Para mi aplicación, BQ pudo manejar aproximadamente 500 columnas por segmento.

Esta es una manera de hacer:

 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 

Esto devuelve:

 +-----+------------+-------+------+--------+------+-------+---+ | 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 | | +-----+------------+-------+------+--------+------+-------+---+ 

Consulte el manual para ver otro ejemplo de tabla dinámica .