Transform table structure of SQL query result (pivot)

Question

I have a table coming from a query with the following content structure:

+-----+-----------+-------+
|date | sensor_id | power |
+-----+-----------+-------+
|date1|     1     |  0.51 |
|date1|     2     |  0.52 |
|date1|     3     |  0.53 |
|date2|     1     |  0.61 |
|date2|     2     |  0.61 |
|date2|     3     |  0.63 |
|date3|     1     |  0.71 |
|date3|     2     |  0.72 |
|date3|     3     |  0.73 |
+-----+-----------+-------+

Is there a way to transform this table in to the following by using SQL?

+-----+-----------+------------+-----------+
|date |    1      |     2      |     3     |
+-----+-----------+------------+-----------+
|date1|  0.51     |    0.52    |   0.53    |
|date1|  0.61     |    0.62    |   0.63    |
|date1|  0.71     |    0.72    |   0.73    |
+-----+-----------+------------+-----------+

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — jpw, Feb 13 '15 at 10:33
I agree it is a duplicate but I did not knew about the pivot table name, I just discovered it here. — Santi Peñate-Vera, Feb 13 '15 at 10:38
No worries, now you do know about it. See this btw: http://www.sqlfiddle.com/#!2/eb7d09/1 — jpw, Feb 13 '15 at 10:46
Would it be possible to create the columns of the query you made dynamically, this is without knowing in at design time the classes of the sensors? — Santi Peñate-Vera, Feb 13 '15 at 13:26

score 1 · Accepted Answer · answered Feb 13 '15 at 13:37

If you know what the set of possible values for sensor_id is beforehand you can use conditional aggregation to do the pivot:

select 
  date,
  max(case when sensor_id = 1 then power else 0 end) as "1",
  max(case when sensor_id = 2 then power else 0 end) as "2",
  max(case when sensor_id = 3 then power else 0 end) as "3"
from Table1
group by date;

If you don't know all the possible values you can use dynamic sql to build an appropriate statement; the query is specific to MySQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(CASE WHEN sensor_id = ',
      sensor_id,' 
      THEN power ELSE 0 END) AS "', 
      sensor_id,'"'
    )
  ) INTO @sql FROM Table1;

SET @sql = CONCAT(
  'SELECT date, ', 
  @sql, 
  ' FROM Table1 GROUP BY date'
);

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

See this SQL Fiddle for examples.

score 0 · Answer 2 · answered Feb 13 '15 at 10:30

0

Yes it is possible and is calles "Pivot" Check this .. Pivot in Mysql

answered Feb 13 '15 at 10:30

PLSQL_007

215
2
8

score -3 · Answer 3 · answered Feb 13 '15 at 10:31

-3

select * 
from table
group by sensor_id

answered Feb 13 '15 at 10:31

isebarn

3,812
5
22
38

This would not produce the desired output and is simply not a valid answer to the question. – jpw Feb 13 '15 at 10:33

Transform table structure of SQL query result (pivot)

3 Answers3