0

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?

ddd
  • 4,665
  • 14
  • 69
  • 125
  • If the result is quite sparse that will be a waste of memory. Have you considered manipulating a set of time series (one per sensor) instead of a matrix ? If you form time series while pulling the rows, then your data would be pivoted. – user2682877 Aug 07 '19 at 10:12

2 Answers2

1

You could use LEFT JOIN:

SELECT DISTINCT t.timestamp, t1.val AS sensor_1, t2.val AS sensor_2, t3.val as sensor_3
FROM (SELECT DISTINCT timestamp FROM tab) t
LEFT JOIN tab t1
  ON t.timestamp = t1.timestamp
  AND t1.sensor_id = 'sensor1'
LEFT JOIN tab t2
  ON t.timestamp = t2.timestamp
  AND t2.sensor_id = 'sensor2'
LEFT JOIN tab t3
  ON t.timestamp = t3.timestamp
  AND t3.sensor_id = 'sensor3'
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I thought about this approach, but there are hundreds of tags. – ddd Aug 02 '19 at 17:03
  • @ddd In any approach you have to know column list upfront. Other options are to build query dynamically or in application layer. – Lukasz Szozda Aug 02 '19 at 17:04
  • @ddd [Accomplish pivot in teradata sql](https://stackoverflow.com/a/22921124/5070879) You should add to your question that you are looking for **dynamic PIVOT** because it is classic example of [XY Problem](https://en.wikipedia.org/wiki/XY_problem) – Lukasz Szozda Aug 02 '19 at 17:06
1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    If you don't know the column names, then you need dynamic SQL, which means a stored procedure. – Andrew Aug 02 '19 at 17:30
  • I ended up using this one but used a script to generate the sql string because there are so many of the tags – ddd Aug 07 '19 at 15:16