Let’s say we have a pivoted result set like below.
We might want to compute the average for each state across the pivot columns. To do that, we’d need to sum of the values across each row, and then divide that by the number of columns.
An Elegant Solution
We’ll take advantage of the
pivot_row function to help us dynamically sum and get the right column number.
pivot_row takes all values across a column and returns a concatenated list. First we’ll use this to get a list of all the values across each row:
Then we’ll take the mean of this list.
Now we have an elegant average!