CODEWITHSUNDEEP
sqlpostgresqltransposecrosstab
Anmol Bhatia
Anmol Bhatia

Reputation: 3

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

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.

Upvotes: 0

Views: 69

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656311

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:

  1. Generate the query.
  2. Execute it.

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:

Upvotes: 1

Related Questions