The New Pivot Function in Bigquery

The new PIVOT function in BigQuery

Use below - it dynamically builds pivot columns

execute immediate (             
select '''select * from (select * from `project.dataset.Produce`)
  pivot(sum(sales) for quarter in ("''' ||  string_agg(quarter, '", "')  || '''"))
'''
from (select distinct quarter from `project.dataset.Produce` order by quarter) 
);

How to Pivot table in BigQuery

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
)

How to pivot in bigQuery using PIVOT?

Consider below approach

execute immediate (select '''
  select *
  from your_table
  pivot (any_value(sale_amt) for replace(sub_class_desc, ' ', '_') in (''' || list || '''))
  '''
  from (
    select string_agg(distinct "'" || replace(sub_class_desc, ' ', '_') || "'") list
    from your_table
  )
)     

if applied to dummy data as in your question - output is

How can I save these results into a new pivoted table? Specifically where can I put my CREATE OR REPLACE TABLE?

execute immediate (select '''
  create or replace table `your_project.your_dataset.pivot_table` as 
  select *
  from your_table
  pivot (any_value(sale_amt) for replace(sub_class_desc, ' ', '_') in (''' || list || '''))
  '''
  from (
    select string_agg(distinct "'" || replace(sub_class_desc, ' ', '_') || "'") list
    from your_table
  )
);

How to pivot multiple columns in BigQuery Standard SQL

Consider below approach

select * from (
  select * except(date)
  from your_table
)
pivot (sum(metric1) as metric1, sum(metric2) as metric2 for iso_year in (2021, 2020, 2019))        

if applied to sample data in your question - output is

Big query PIVOT operator, how do I make it work when column data type is STRING and I cannot apply aggregate functions

Try aggregate functions that work with strings. For example MAX:

with Produce AS (
  SELECT 'Kale' as product, 'good' as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 'bad', 'Q2' UNION ALL
  SELECT 'Kale', 'good', 'Q3' UNION ALL
  SELECT 'Kale', 'bad', 'Q4' UNION ALL
  SELECT 'Apple', 'bad', 'Q1' UNION ALL
  SELECT 'Apple', 'good', 'Q2' UNION ALL
  SELECT 'Apple', 'bad', 'Q3' UNION ALL
  SELECT 'Apple', 'good', 'Q4')
SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(MAX(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

Big Query Pivot multiple columns in 2 columns

You can use the UNPIVOT operator for this:

CREATE TEMP TABLE t (
  solution STRING,
  sentiment STRING,
  `groups` ARRAY<STRING>,
  feeling BOOLEAN,
  playing BOOLEAN,
  doing BOOLEAN
);

INSERT INTO t
  (solution, sentiment, `groups`, feeling, playing, doing)
VALUES
  ('I am good', 'positive', ['good', 'am'], true, false, false),
  ('I am playing', 'positive', ['playing', 'am'], false, true, true),
  ('She is running', 'positive', ['running', 'she'], false, true, false),
  ('He is not eating', 'negative', ['eating'], true, false, true);

SELECT *
FROM t UNPIVOT(value FOR name IN (feeling, playing, doing));

returns

solution    sentiment   groups  value   name
He is not eating    negative    [eating]    true    feeling
He is not eating    negative    [eating]    false   playing
He is not eating    negative    [eating]    true    doing
I am good   positive    "[good,am]" true    feeling
I am good   positive    "[good,am]" false   playing
I am good   positive    "[good,am]" false   doing
She is running  positive    "[running,she]" false   feeling
She is running  positive    "[running,she]" true    playing
She is running  positive    "[running,she]" false   doing
I am playing    positive    "[playing,am]"  false   feeling
I am playing    positive    "[playing,am]"  true    playing
I am playing    positive    "[playing,am]"  true    doing

---

Your idea of using UNNEST can also work, you just need to keep both name and value in a single array:

SELECT solution, sentiment, `groups`, name, value
FROM t, 
UNNEST (
    ARRAY<STRUCT<name STRING, value BOOLEAN>>[('feeling', feeling), ('playing', playing), ('doing', doing)]
) ;

---

Related Topics