SELECT from "vertical" table and display on "horizontal" table 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-&gt;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 &lt; count($selectcityID _array_unique); ++$i){ /*for every city*/
   for ($i_content=0; $i_content &lt; count($selectparameterID_array_unique); ++$i_content){ /*and for every parameter*/
        echo $ value_array_chunked[$i][$i_content];
        echo ' ';
     }

     echo '&lt;br&gt;';

} 


It works, but is this the only (and best) way to do it?
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