Stack Exchange
BigQuery - PIVOT with declared ARRAY
Pivoting like this works
BEGIN
    CREATE TEMP TABLE SOF_SAMPLE
    AS  SELECT 1 id, 'BANANA' fruit, 2 value
    UNION ALL SELECT 2, 'ORANGE', 10
    UNION ALL SELECT 3, 'APPLE', 3;
END;
SELECT * FROM (
  SELECT ID, VALUE, FRUIT
  FROM `SOF_SAMPLE`
  WHERE 
    1=1
    AND FRUIT IN ('BANANA', 'APPLE')
)
PIVOT (
  AVG(VALUE)
  FOR FRUIT IN ('BANANA', 'APPLE')
)

However it seems that PIVOT .. FOR i IN does not accept UNNEST (Syntax error: Unexpected ")" at)
DECLARE FRUITS ARRAY <STRING> DEFAULT ['BANANA', 'APPLE'];

SELECT * FROM (
  SELECT ID, VALUE, FRUIT
  FROM `SOF_SAMPLE`
  WHERE 
    1=1
    AND FRUIT IN UNNEST(FRUITS)
)
PIVOT (
  AVG(VALUE)
  FOR FRUIT IN UNNEST(FRUITS)
)


It's not allowed to use an array as pivot columns, so most general way to to is to write a dynamic sql. Consider below query.
EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM (
  SELECT ID, VALUE, FRUIT FROM `SOF_SAMPLE` WHERE FRUIT IN ('%s')
)
PIVOT (AVG(VALUE) FOR FRUIT IN ('%s'))
"""
, ARRAY_TO_STRING(FRUITS, "','")
, (SELECT STRING_AGG(FRUITS, "','") FROM SOF_SAMPLE));



is it possible to use UNNEST(FRUITS) in the WHERE clause ?

@J.Doe, I don't think so.  it should be used along with FROM clause.

it is possible to use UNNEST(FRUITS) in the WHERE clause using ARRAY_TO_STRING(FRUITS, "','")

yes, you can use ARRAY_TO_STRING() in WHERE clause, but not along with UNNEST().
