PIVOTing BigQuery tables on DATE type

birdx0810 0
Viewed 64

I'm trying to pivot a table with each row being a transaction with a date. E.g.

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, DATE('2020-01-01') as dates UNION ALL
  SELECT 'Kale', 23, DATE('2020-01-02') UNION ALL
  SELECT 'Kale', 45, DATE('2020-01-03') UNION ALL
  SELECT 'Kale', 3,  DATE('2020-01-04') UNION ALL
  SELECT 'Apple', 70, DATE('2020-01-01') UNION ALL
  SELECT 'Apple', 85, DATE('2020-01-02') UNION ALL
  SELECT 'Apple', 77, DATE('2020-01-03') UNION ALL
  SELECT 'Apple', 1, DATE('2020-01-04')
)

My query is something like this.

SELECT * FROM Produce
PIVOT(sum(sales) FOR dates IN (DATE('2020-01-01'), DATE('2020-01-02'), DATE('2020-01-03'), DATE('2020-01-04')))

However BigQuery returns the following error.

Generating an implicit alias for this PIVOT value is not supported; please provide an explicit alias at [14:41]

According to the docs, pivot columns with type of date should be able to generate alias implicitly, or is my understanding wrong?

google-bigquery
2 Answers

Use below instead

SELECT * FROM Produce
PIVOT(sum(sales) FOR dates IN ('2020-01-01','2020-01-02', '2020-01-03', '2020-01-04'))

with output

enter image description here

Mikhail Berlyant0

From Rules for pivot_column:

A pivot_column must be a constant.

DATE('2020-01-01') is an expression, not a constant. So you need to use one of followings.

PIVOT(sum(sales) FOR dates IN (DATE '2020-01-01', ...) -- explicit DATE literal
-- or
PIVOT(sum(sales) FOR dates IN ('2020-01-01', ...) -- literal implicitly coerced to DATE type
-- or
PIVOT(sum(sales) FOR dates IN (DATE('2020-01-01') AS _2020_01_01, ...) -- alias
Dynamic SQL Example
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM Produce
   PIVOT (SUM(sales) FOR dates IN (%s))
""", TRIM(TO_JSON_STRING(GENERATE_DATE_ARRAY('2020-01-01', '2020-01-4')), '[]'));

enter image description here

Jaytiger0
Related