When we have a result set like the one below, we might want to compute the average (mean) for each state across the pivot columns:
To do that, we need to find the 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 the values from all columns across a row and returns a concatenated list.
First, we’ll use this to get a list of all the values across each row:
Then, we’ll generate an average by taking the mean of this list:
Now, we have an elegant average of the pivot row, as shown above.