Pivot table with sql-CodePudding

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|

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

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

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