Skip to content
Advertisement

How to pivot time series table without aggregation

I am pulling sensor data from a Teradata table for analysis. Below is what the table looks like. enter image description here

I want to pivot it such that sensor names become columns. enter image description here

There are more than a hundred sensors and thus that many columns in the matrix after the pivot. The final result set will be quite sparse since not all sensors have values for all the dates. How do I pivot the table without aggregation?

Advertisement

Answer

What is wrong with aggregation?

select timestamp,
       max(case when sensor_id = 'sensor1' then val end) as sensor1,
       max(case when sensor_id = 'sensor2' then val end) as sensor2,
       max(case when sensor_id = 'sensor3' then val end) as sensor3,
       . . .
from t
group by timestamp;

This seems like this simplest way to express the logic. And it probably performs a bit better than 100 joins.

4 People found this is helpful
Advertisement