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

Question

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?

user_5221944 · Answer

Consider below

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

user_13473525 · Answer

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 operator, how do I make it work when column data type is STRING and I cannot apply aggregate functions

2 Answers