0

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.

Anmol Bhatia
  • 3
  • 1
  • 5
  • Does this answer your question? [PostgreSQL Crosstab Query](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query) – nbk Aug 15 '22 at 14:38
  • I checked this link out, but, it doesn't suit my case as the number of propertyId values is dynamic. – Anmol Bhatia Aug 18 '22 at 08:33

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228