mysql time series data to pivot table

Question

I have the following data in a table, and need to get a pivot table output. I can't for the life of me figure this out. Thanks in advance.....

Rows in the mysql table

id, timestamp, metric, value
1, 2020-10-31 02:52:02.144030, aaaa, 3
2, 2020-10-31 02:52:02.144030, tttt, 40
3, 2020-10-31 02:52:02.144030, vvvv, 6
4, 2020-10-31 02:53:01.656349, aaaa, 3
5, 2020-10-31 02:53:01.656349, tttt, 40
6, 2020-10-31 02:53:01.656349, vvvv, 6
7, 2020-10-31 02:54:02.053597, aaaa, 3
8, 2020-10-31 02:54:02.053597, tttt, 40
9, 2020-10-31 02:54:02.053597, vvvv, 6
10, 2020-10-31 02:55:01.490467, aaaa, 3
11, 2020-10-31 02:55:01.490467, tttt, 40
12, 2020-10-31 02:55:01.490467, vvvv, 6

Output I am looking for

timestamp, aaaa, tttt, vvvv
2020-10-31 02:52:02.144030, 3, 40, 6
2020-10-31 02:53:01.656349, 3, 40, 6
2020-10-31 02:54:02.053597, 3, 40, 6
2020-10-31 02:55:01.490467, 3, 40, 6

Consider handling issues of data display in application code — Strawberry, Oct 31 '20 at 08:16

Barbaros Özhan · Answer 1 · 2020-10-31T08:42:22.600

1

You can pivot dynamically through use of GROUP_CONCAT() and CONCAT() functions such as

SET @sql = NULL;

SELECT GROUP_CONCAT(                 
             CONCAT(
                    'MAX(CASE WHEN metric = "', metric,'" THEN value END ) AS ',metric
                    )
       )
  INTO @sql
  FROM ( SELECT DISTINCT metric FROM tab) t;

SET @sql = CONCAT('SELECT timestamp,',@sql,' FROM tab GROUP BY timestamp'); 

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

Demo

edited Oct 31 '20 at 08:42

answered Oct 31 '20 at 08:32

Barbaros Özhan

59,113
10
31
55

Thanks. I am going to have to sit down and figure out how this works to understand it. – Erik Sundberg Nov 02 '20 at 20:51
you're welcome @ErikSundberg – Barbaros Özhan Nov 02 '20 at 20:53

mysql time series data to pivot table

1 Answers1