Pivot table with sql

Question

Is possible to pivot this table directly in a single sql query? (the table is the result of a query)

From this:

| timestamp            | sensor_id | value |
|----------------------|-----------|-------|
| 2021-09-09 02:00 +02 | 58        | 31.7  |
| 2021-09-09 02:00 +02 | 60        | 45.8  |
| 2021-09-09 03:00 +02 | 58        | 81.9  |
| 2021-09-09 03:00 +02 | 60        | 100.8 |

to this:

| timestamp            | 58  | 60   |
|----------------------|-----|------|
| 2021-09-09 02:00 +02 | 31.7| 45.8 |
| 2021-09-09 03:00 +02 | 81.9| 100.8|

This is the actual query that create the first table

select TIMESTAMP AT TIME ZONE 'CETDST' AS TIMESTAMP,sensor_id,value
FROM measure
WHERE sensor_id IN (58,60)
AND TIMESTAMP AT TIME ZONE 'CETDST' BETWEEN '2021-09-09 02:00' AND '2021-09-09 03:00' 
ORDER BY TIMESTAMP

Is it possible? Can you help me? PostgreSQL v.12

score 1 · Answer 1 · answered Jan 25 '22 at 17:03

Try this - this is also solid if you have rows with sensor id 58, and no rows with sensor id 60 at the same timestamp - just by filling up the resulting NULLS with a LAST_VALUE(... IGNORE NULLS) OLAP function call:

I added some rows with gaps to your in - data:

WITH
-- your input - don't use in final query ...
indata(ts,sensor_id,val) AS (
          SELECT TIMESTAMP '2021-09-09 02:00',58, 31.7
UNION ALL SELECT TIMESTAMP '2021-09-09 02:00',60, 45.8
UNION ALL SELECT TIMESTAMP '2021-09-09 03:00',58, 81.9
UNION ALL SELECT TIMESTAMP '2021-09-09 03:00',60,100.8
UNION ALL SELECT TIMESTAMP '2021-09-09 04:00',58,131.7
UNION ALL SELECT TIMESTAMP '2021-09-09 05:00',58,181.9
UNION ALL SELECT TIMESTAMP '2021-09-09 06:00',60,200.8
UNION ALL SELECT TIMESTAMP '2021-09-09 07:00',60,245.8
)
-- REAL QUERY STARTS HERE - note "ts" and "val" as column names - avoid keywords                                                                                                                          
SELECT
  ts
, LAST_VALUE(MAX(CASE sensor_id WHEN '58' THEN val END) IGNORE NULLS) OVER w AS val_58
, LAST_VALUE(MAX(CASE sensor_id WHEN '60' THEN val END) IGNORE NULLS) OVER w AS val_60
FROM indata
GROUP BY
  ts
WINDOW w AS(ORDER BY ts)
-- out          ts          | val_58 | val_60 
-- out ---------------------+--------+--------
-- out  2021-09-09 02:00:00 |   31.7 |   45.8
-- out  2021-09-09 03:00:00 |   81.9 |  100.8
-- out  2021-09-09 04:00:00 |  131.7 |  100.8
-- out  2021-09-09 05:00:00 |  181.9 |  100.8
-- out  2021-09-09 06:00:00 |  181.9 |  200.8
-- out  2021-09-09 07:00:00 |  181.9 |  245.8

What's the error message? It could be that your DBMS does not support named windows. If that is the case, change the `OVER w` clause of the `LAST_VALUE()` calls to `OVER(ORDER BY ts)` and remove the `WINDOW w` clause at the bottom — marcothesane, Jan 27 '22 at 16:27
this is how looks like now: SELECT ts , LAST_VALUE(MAX(CASE sensor_id WHEN '11001' THEN val END) IGNORE NULLS) OVER(ORDER BY ts) AS sens_1 , LAST_VALUE(MAX(CASE sensor_id WHEN '11002' THEN val END) IGNORE NULLS) OVER(ORDER BY ts) as sens_2 FROM (select TIMESTAMP AT TIME ZONE 'CETDST' AS ts,sensor_id,VALUE AS val FROM measure WHERE sensor_id IN (11001,11002) AND TIMESTAMP AT TIME ZONE 'CETDST' BETWEEN '2022-01-01 00:00:00' AND '2022-01-02 00:00:00' ORDER BY TIMESTAMP) GROUP BY ts — mat, Jan 27 '22 at 16:56

score 1 · Answer 2 · answered Jan 25 '22 at 17:03

If you use PANDAS on python you can do it easily. But in SQL you can use

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when sensor_id = ''',
      sensor_id,
      ''' then value end) ',
      sensor_id
    )
  ) INTO @sql 
FROM
  your_table;
SET @sql = CONCAT('SELECT timestamp, ', @sql, ' 
                  FROM your_table 
                   GROUP BY timestamp');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

yes sure with pandas is very easy but I do not get how this actually works — mat, Jan 27 '22 at 14:07

score 1 · Answer 3 · answered Jan 25 '22 at 17:10

1

you have here below two examples for implementing a full dynamic pivot-table solution :

https://stackoverflow.com/a/70466824/8060017

https://stackoverflow.com/a/70695023/8060017

answered Jan 25 '22 at 17:10

Edouard

6,577
1
9
20

Pivot table with sql

3 Answers3