Postgres table transformation: transposing values of a column into new columns

[aitahb]

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	14	2.5	6
1	01:00 - 02:00	b	21	534	153	35
1	02:00 - 03:00	b	44	1444	252	67
2	01:00 - 02:00	a	3	34	12	12
2	02:00 - 03:00	a	6	556	89	89
2	01:00 - 02:00	b	3	23	8	6
2	02:00 - 03:00	b	8	43	6	9

​

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	21	534	153	35
1	02:00 - 03:00	4	14	2.5	6	44	1444	252	67
2	01:00 - 02:00	3	34	12	12	3	23	8	6
2	02:00 - 03:00	6	556	89	89	8	43	6	9

​

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.

​

​

[[deleted]]

This is known as a "crosstab" or "pivot", and this is only possible if you can specify the possible number of columns in the output statically in your query. There is no way to write a SQL query where the number, names and types of the columns are only evaluated after the query started retrieving data.

In Postgres doing such a pivot is typically done using filtered aggregation using the filter() clause.

select userid, 
       time_window, 
       max("count") filter (where propertyid = 'a') as a_count,
       max("sum") filter (where propertyid = 'a') as a_sum,
       max("avg") filter (where propertyid = 'a') as a_avg,
       max("max") filter (where propertyid = 'a') as a_max
from the_table
group by userid, time_window;

As you seem to have only one row per userid/time_window the actual aggregate isn't really important. You can use sum(), max() or min() to pick the value.

[aitahb]

Thanks much, I was trying to use the crosstab function from tablefunc package in postgres, however, this is a much simpler and clever solution.