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:

tried in my dev envt

• codeispoetry: Thanks Ceeee! I only don't understand why I need the the last line DELETE...
• Ceeee: that's for my testing only, dont mind the insert and delete :D just checked if the query works even if one city do not have ony of temp/humidity/rain