CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
radhika sharma
radhika sharma

Reputation: 569

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

I was trying to understand and work on big query new pivot operator. From this another post How to Pivot table in BigQuery, I understand how it works. The example in google documentation that is mentioned about data having product, sales and quarter and how we get Pivoted data using query below.

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple   | 77 | 0  | 25 | 2  |
| Kale    | 51 | 23 | 45 | 3  |
+---------+----+----+----+----+

I am wondering what if I have to PIVOT a data for data example below using PIVOT operator where the sales data is STRING column and had below data. This is just for an example. I cannot provide real time data here as its sensitive data.

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | good  | Q1      |
| Kale    | bad   | Q2      |
| Kale    | good  | Q3      |
| Kale    | bad   | Q4      |
| Apple   | bad   | Q1      |
| Apple   | good  | Q2      |
| Apple   | bad   | Q3      |
| Apple   | good   | Q4     |
+---------+-------+---------+

And the output should be as below

+---------+------+-----+-----+----+
| product | Q1   | Q2  | Q3  | Q4 |
+---------+------+-----+-----+----+
| Apple   | bad  | good| bad | good|
| Kale    | good | bad | good| bad |
+---------+------+-----+-----+-----+

In this case sum will not work, neither casting will work. How should we use PIVOT operator in such cases?

Upvotes: 2

Views: 10995

Answers (2)

Sergey Geron
Sergey Geron

Reputation: 10222

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'))

enter image description here

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Consider below

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(STRING_AGG(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

Upvotes: 4

Related Questions