Dynamic Pivot Columns | How to generate dynamic Pivot Columns in BigQuery

How could i connect this to sheets?

Great Video!

Awesome! Congrats for the videos! Keep posting to the BigQuery series please!!! Cheers

Thank you much for the tutorial! Just what I need it. Now, how can you create an new table from the results in a way you can do it scheduled? I believe that would be using STORE PROCEDUREs with this scripting, but I don't know what would be the syntax to store the results into a final TABLE that can be reconstructed every time I run the PROCEDURE. I tried using SCHEDULED QUERIES but the scripting is not allowed there. Any help would be much appreciated.

Thansk,
can I use this with a CTE?

I get an error. Invalid format specifier character "R" in FORMAT string. Anyone can help?

DECLARE MONTHS STRING;
SET MONTHS = (
SELECT
CONCAT('("', STRING_AGG(DISTINCT cast(MONTH as string), '", "'), '")'),
FROM (
select distinct date_trunc(jakarta_data_date, month) MONTH,
from `data-gojek-id-mart.corporate_strategy.detail_booking` a
where jakarta_data_date >= date_sub(date_trunc(current_date('Asia/Jakarta'), month), interval 3 month)
order by 1
)
);

select MONTHS;


EXECUTE IMMEDIATE format("""
SELECT * FROM
(
select
date_trunc(jakarta_data_date, month) AS MONTH,
sa.service_area_name AS CITY,
regexp_extract(UPPER(a.service_type_name),'(CAR|RIDE|FOOD)') as service_type_name,
count(distinct order_no) AS BOOKING,

from `data-gojek-id-mart.corporate_strategy.detail_booking` a
left join `data-gojek-id-presentation.location.dim_service_area` sa on a.service_area_id = sa.service_area_id

where jakarta_data_date >= date_sub(date_trunc(current_date('Asia/Jakarta'), month), interval 3 month)
and (upper(a.service_type_name) like '%RIDE%' OR upper(a.service_type_name) like '%CAR%' OR upper(a.service_type_name) like '%FOOD%')
and upper(sa.service_area_name) = 'BANDUNG'

group by 1,2,3

)

PIVOT
(SUM(BOOKING) FOR MONTH in(%s))""", MONTHS);

Exactly what I was looking for with an easy-to-understand example! Thanks!

Thank you for the wonderful explanation. Is there a similar way by which we can dynamically aggregate string columns in bigquery sql? Usually we would just use Select STRING_AGG(col_1), col_2 From table1 Group BY col_2. What if the number of columns are increasing dynamically?

Thank you!!!! It reallly helps a lot.

Tks a lot!