Search code examples
sqlgroup-bypivotprestodynamic-pivot

How to pivot a table in Presto?

Let be a table named data with columns time, sensor, value :

enter image description here

I want to pivot this table on Athena (Presto) to get a new table like this one :

enter image description here

To do so, one can run the following query :

SELECT time, 
sensor_value['temperature'] as "temperature", 
sensor_value['pressure'] as "pressure" 
FROM (
    SELECT time, mapp_agg(sensor, value) sensor_value
    FROM data
    GROUP BY time
)

This works well. But, I must specify the keys of sensor_value. I thus need to know the unique values of sensor to then manually write the query accordingly. The problem is that I don't have such information. Do you know a generic (and efficient) solution to solve this issue ? I would really appreciate any help. Thanks.

Solution

  • This will give you the answer, you just have to create a row_number to pivot your table.

    SELECT 
    TIME, 
    MAX(CASE WHEN SENSOR='TEMPERATURE' THEN VALUE END) AS TEMPERATURE,
    MAX(CASE WHEN SENSOR='PRESSURE' THEN VALUE END) AS PRESSURE
 FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY SENSOR ORDER BY TIME) AS ROW_GROUP FROM TEMP_PRESSURE)
GROUP BY ROW_GROUP