Is there a way to transpose/flatten the following table -
| userId | time window | propertyId | count | sum | avg | max |
|---|---|---|---|---|---|---|
| 1 | 01:00 - 02:00 | a | 2 | 5 | 1.5 | 3 |
| 1 | 02:00 - 03:00 | a | 4 | 15 | 2.5 | 6 |
| 1 | 01:00 - 02:00 | b | 2 | 5 | 1.5 | 3 |
| 1 | 02:00 - 03:00 | b | 4 | 15 | 2.5 | 6 |
| 2 | 01:00 - 02:00 | a | 2 | 5 | 1.5 | 3 |
| 2 | 02:00 - 03:00 | a | 4 | 15 | 2.5 | 6 |
| 2 | 01:00 - 02:00 | b | 2 | 5 | 1.5 | 3 |
| 2 | 02:00 - 03:00 | b | 4 | 15 | 2.5 | 6 |
to something like this -
| userId | time window | a_count | a_sum | a_avg | a_max | b_count | b_sum | b_avg | b_max |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 01:00 - 02:00 | 2 | 5 | 1.5 | 3 | 2 | 5 | 1.5 | 3 |
| 1 | 02:00 - 03:00 | 4 | 15 | 2.5 | 6 | 4 | 15 | 2.5 | 6 |
| 2 | 01:00 - 02:00 | 2 | 5 | 1.5 | 3 | 2 | 5 | 1.5 | 3 |
| 2 | 02:00 - 03:00 | 4 | 15 | 2.5 | 6 | 4 | 15 | 2.5 | 6 |
Basically, I want to flatten the table by having the aggregation columns (count, sum, avg, max) per propertyId, so the new columns are a_count, a_sum, a_avg, a_max, b_count, b_sum, ... All the rows have these values per userId per time window.
Important clarification: The values in propertyId column can change and hence, the number of columns can change as well. So, if there are n different values for propertyId, then there will be n*4 aggregation columns created.
SQL does not allow a dynamic number of result columns on principal. It demands to know number and data types of resulting columns at call time. The only way to make it "dynamic" is a two-step process:
If you don't actually need separate columns, returning arrays or document-type columns (json, jsonb, xml, hstore, ...) containing a variable number of data sets would be a feasible alternative.
See: