repeat the same aggregation on different columns in BigQuery

Question

I have a table like below:

product  |  weight   |  col1   |    col2   |   col3   | ...  |   col100
productA |  1        |  1      |    2      |   3      | ...  |   100
productB |  1        |  1      |    2      |   3      | ...  |   100
productA |  2        |  0.5    |    20     |   3      | ...  |   200
productB |  3        |  0.5    |    20     |   3      | ...  |   200

I want to write a query that calculates the weighted average of columns 1 to 100 using column weight for each product. The query is simple:

SELECT 
     product,
     SUM(weight*col1)/SUM(weight) OVER(partition by product) AS wighted_average_col1
FROM product_tbl

But I don't want to repeat the weighted average line 100 times for each column. Is there a way to create a loop here to iterate through the columns and create an output like below: I have a table like below:

product  |  wighted_average_col1   |  wighted_average_col2   |    wighted_average_col3   ...  |   wighted_average_col100
productA |  0.33                   |  x                      |    y                           |   z
productB |  0.37                   |  n                      |    m                           |   l

Answer 1

As mentioned by @Mikhail Berlyant, you can use EXECUTE IMMEDIATE! to dynamically build a sql for it.

You can follow this documentation for more information about executing a dynamic SQL statement.

You can also refer to this link which is one of the examples for your requirement

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.