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.

Answer

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;

Advertisement

Answer