I've built a dynamic pivot table in BigQuery that does what I need with an exception.
The final goal is to also obtain a sum of each row's items' total in a supplemental column that is not part of the dynamic columns' set passed to the pivot call.
I've tried using window functions and partition without success.
Any idea?
Below a small extract of the data
Product,agent,qty
Apple,Jon,10
Banana,Jon,5
Apple,Mark,20
Even if I can SUM in the inner query the total quantity for each agent, I couldn't figure out how to expose in the PIVOT IN. Everything I've tried so far returns just 1, as the single line of items, but not the sun (e.g Jon, 15, and Mark 20)
Any idea?
As mentioned by @Andrea Moro and @samuel in the above comments, you can make use of CTE, each table in these WITH statements can be addressed. You can create one CTE table with the PIVOT and use this table to work with the window function.
To get the expected result with the required nesting, a possible solution could be that you can use a group by, at the end in between with dynamic columns regexp_replaced with non-necessary characters or replaced with backtick to make them columns.
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.