SELECT from "vertical" table and display on "horizontal" table

Question

I have this table in my db:

table
----------
cityID
value 
parameterID

The table looks like this:

cityID     value      parameterID
500        30         1
500        60         2
500        7          3
600        25         1
600        50         2
600        6          3   
....       ...        .....

where 1,2, 3 stands for temperature, humidity and cm of rain.

I need to display this data this way:

   city       temp        humidity       rain
   500        30           60            7
   600        25           50            6

What I did was this (after SELECT * FROM table):

 /*retrieve all values and put them in loooong arrays */

while($row = $result->fetch_assoc()) {      
$selectcityID_array[] = (isset ($row['cityID']) ? $row['cityID'] : "");
$selectparameterID_array[] = (isset ($row['parameterID']) ? $row['parameterID'] : "");  
$selectvalue_array[] = (isset ($row['value']) ? $row['value'] : "");    
 }
/* remove doubles from cities and parameters*/
$selectcityID_array_unique = array_values (array_unique($selectcityID_array) );
$selectparameterID_array_unique = array_values (array_unique($selectparameterID_array) );

/*chunk the values do that I have an "horizontal" array for every city*/
$value_array_chunked = array_chunk($selectvalue_array, count($selectparameterID_array_unique));


for ($i=0; $i < count($selectcityID _array_unique); ++$i){ /*for every city*/
   for ($i_content=0; $i_content < count($selectparameterID_array_unique); ++$i_content){ /*and for every parameter*/
        echo $ value_array_chunked[$i][$i_content];
        echo ' ';
     }

     echo '<br>';

} 

It works, but is this the only (and best) way to do it?

Answer 1

You can use conditional aggregation to get the expected result set:

SELECT cityID, 
       MAX(CASE WHEN parameterID = 1 THEN value END) AS temp,
       MAX(CASE WHEN parameterID = 2 THEN value END) AS humidity,
       MAX(CASE WHEN parameterID = 3 THEN value END) AS rain
FROM mytable
GROUP BY cityID

Demo here

Answer 2

You may also join the table to itself:

SELECT DISTINCT
tablea.cityID AS city,
ttemp.value AS temp,
thumid.value AS humidity,
train.value AS rain
FROM tablea
LEFT JOIN tablea AS ttemp
ON ttemp.cityID = tablea.cityID AND ttemp.parameterID = 1
LEFT JOIN tablea AS thumid
ON thumid.cityID = tablea.cityID AND thumid.parameterID = 2
LEFT JOIN tablea AS train
ON train.cityID = tablea.cityID AND train.parameterID = 3

below is my testing: