How to use dynamic pivot table in bigquery?

user_4867317 0
Viewed 2124

My goal is to create a dynamic pivot table in Bigquery, to do so I was planning on:

  • Declaring an array based on a select distinct
  • Using this array in the pivot statement FOR .. IN UNNEST(ARRAY)

Here is the sample code :

DECLARE cat ARRAY <STRING> ;
SET cat = ( SELECT  ARRAY_AGG( distinct product_category_level_3) FROM categories );


SELECT *
FROM (
    SELECT
        customer_id
        , product_category_level_3
        , last_variant_bought_id

    FROM table
)PIVOT(
    MAX(last_variant_bought_id)
    FOR product_category_level_3 IN UNNEST(cat)
)

However this raise a Syntax error: Unexpected ")" at the end of the query. I cannot figure out why is it happening ? Any thought on this error or the best approach to do this ?

Best

google-bigquery sql
1 Answers

Need to declare STRING variable, then EXECUTE_IMMEDIATE

DECLARE cat STRING ;
SET cat = ( SELECT  STRING_AGG( distinct product_category_level_3) FROM categories );

EXECUTE_IMMEDIATE """
SELECT *
FROM (
    SELECT
        customer_id
        , product_category_level_3
        , last_variant_bought_id

    FROM table
)PIVOT(
    MAX(last_variant_bought_id)
    FOR product_category_level_3 IN (?)
)
"""
USING cat
user_124726440
Related