google cloud platform - Big query PIVOT operator, how do I make it work when column data type is STRING and I cannot apply aggregate functions - Stack Overflow

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