How to Pivot table in BigQuery

Question

I am using Google Big Query, and I am trying to get a pivoted result out from public sample data set.

A simple query to an existing table is:

SELECT * 
FROM publicdata:samples.shakespeare
LIMIT 10;

This query returns following result set.

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.

Here is the query that I am using.

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;

But, this query returns the data

What I was looking for is

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.

EDITED: I also referred to How to simulate a pivot table with BigQuery? and it seems it also has the same issue I mentioned here.

Answer 1

Update 2020:

Just call fhoffa.x.pivot(), as detailed in this post:

• https://medium.com/@hoffa/easy-pivot-in-bigquery-one-step-5a1f13c6c710

For the 2019 example, for example:

CREATE OR REPLACE VIEW `fh-bigquery.temp.a` AS (
 SELECT * EXCEPT(SensorName), REGEXP_REPLACE(SensorName, r'.*/', '') SensorName
 FROM `data-sensing-lab.io_sensor_data.moscone_io13`
);

CALL fhoffa.x.pivot(
  'fh-bigquery.temp.a'
  , 'fh-bigquery.temp.delete_pivotted' # destination table
  , ['MoteName', 'TIMESTAMP_TRUNC(Timestamp, HOUR) AS hour'] # row_ids
  , 'SensorName' # pivot_col_name
  , 'Data' # pivot_col_value
  , 8 # max_columns
  , 'AVG' # aggregation
  , 'LIMIT 10' # optional_limit
);

Update 2019:

Since this is a popular question, let me update to #standardSQL and a more general case of pivoting. In this case we have multiple rows, and each sensor looks at a different type of property. To pivot it, we would do something like:

#standardSQL
SELECT MoteName
  , TIMESTAMP_TRUNC(Timestamp, hour) hour
  , AVG(IF(SensorName LIKE '%altitude', Data, null)) altitude
  , AVG(IF(SensorName LIKE '%light', Data, null)) light
  , AVG(IF(SensorName LIKE '%mic', Data, null)) mic
  , AVG(IF(SensorName LIKE '%temperature', Data, null)) temperature
FROM `data-sensing-lab.io_sensor_data.moscone_io13`
WHERE MoteName = 'XBee_40670F5F'
GROUP BY 1, 2

As an alternative to AVG() you can try MAX(), ANY_VALUE(), etc.

---

Previously:

I'm not sure what you are trying to do, but:

SELECT NTH(1, words) WITHIN RECORD column_1, NTH(2, words) WITHIN RECORD column_2, f0_
FROM (
  SELECT NEST(word) words, SUM(c)  
  FROM (
    SELECT word, SUM(word_count) c
    FROM publicdata:samples.shakespeare
    WHERE word in ('brave', 'attended')
    GROUP BY 1
  )
)

UPDATE: Same results, simpler query:

SELECT NTH(1, word) column_1, NTH(2, word) column_2, SUM(c)
FROM (
    SELECT word, SUM(word_count) c
    FROM publicdata:samples.shakespeare
    WHERE word in ('brave', 'attended')
    GROUP BY 1
)