CODEWITHSUNDEEP
mysqlsqlpivot
Larsus
Larsus

Reputation: 111

SQL convert columns in rows

I want convert this table (Reading):

ID  TimeTable_ID    reading_Value   Sensor_ID
1        1               482           1
2        1               153           2
3        1               152           3
4        1               781           4
5        2               156           1
6        2               842           2
7        2               157           3
8        2               453           4

into this:

TimeTable_ID    Sensor_1    Sensor_2    Sensor_3    Sensor_4
     1             482         153         152         781
     2             156         842         157         453

My try:

SELECT * 
FROM (SELECT TimeTable_ID, reading_Value 
      FROM Reading
     ) AS BaseData PIVOT 
     (COUNT(reading_Value) FOR TimeTable_ID IN ([Sensor_1], [Sensor_2], [Sensor_3], [Sensor_4])
     ) AS PivotTable;

but it does not work.

Upvotes: 0

Views: 57

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you have limited Sensors then you can do :

select TimeTable_ID, 
       sum(case when Sensor_ID = 1 then reading_Value else 0 end) as Sensor_1,
       . . . 
       sum(case when Sensor_ID = 4 then reading_Value else 0 end) as Sensor_4 
from reading r
group by TimeTable_ID;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

MySQL does not support the PIVOT operator. But, you may use a standard pivot query instead:

SELECT
    TimeTable_ID,
    MAX(CASE WHEN Sensor_ID = 1 THEN reading_Value END) AS Sensor_1,
    MAX(CASE WHEN Sensor_ID = 2 THEN reading_Value END) AS Sensor_2,
    MAX(CASE WHEN Sensor_ID = 3 THEN reading_Value END) AS Sensor_3,
    MAX(CASE WHEN Sensor_ID = 4 THEN reading_Value END) AS Sensor_4
FROM Reading
GROUP BY TimeTable_ID;

Upvotes: 2

Related Questions