CODEWITHSUNDEEP
mysqlpivot
user1785210
user1785210

Reputation:

Create dynamic table in mySQL

i have a problem with mySQL. I have a table like this:

Time                    Sensor    Value   
2012-10-16 14:42:32 VI0    0      
2012-10-16 14:42:32 VI1    0      
2012-10-16 14:42:32 VI2    0      
2012-10-16 14:42:32 VI3    0      
2012-10-16 14:42:33 VI0    1      
2012-10-16 14:42:33 VI1    1      
2012-10-16 14:42:33 VI2    1      
2012-10-16 14:42:33 VI3    1

i have a table "sensor" with all the name sensor and other information. Is it possible rearrange that table in a table like this:

Time                    VI0        VI1      VI2    VI3

2012-10-16 14:42:32 0      0         0      0

2012-10-16 14:42:32 1      1         1      1

I'm looking the pivot table but i don't know if it is the right way.

P.S. maybe i found the solution:

SELECT time,GROUP_CONCAT(value) as Sensor FROM measure2 GROUP BY time;

time GROUP_CONCAT(value)

2012-10-16 14:42:32 0,0,0,0

Instead of GROUP_CONCAT can i write the name of sensor with comma?

Upvotes: 1

Views: 286

Answers (1)

Taryn
Taryn

Reputation: 247700

It looks to me that you need to use a prepared statement to pivot the data dynamically. This will use an aggregate function with a CASE statement:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when Sensor = ''',
      Sensor,
      ''' then value end) AS ',
      Sensor
    )
  ) INTO @sql
FROM  measure2;

SET @sql = CONCAT('SELECT time, ', @sql, ' 
                  FROM measure2 
                  GROUP BY time');

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

See SQL Fiddle with Demo

If you have known values, then you can hard-code the values:

select time,
  max(case when sensor = 'VI0' then value end) as VI0,
  max(case when sensor = 'VI1' then value end) as VI1,
  max(case when sensor = 'VI2' then value end) as VI2,
  max(case when sensor = 'VI3' then value end) as VI3
from measure2
group by time

See SQL Fiddle with Demo

Upvotes: 3

Related Questions