使用SQL将行元素分组到一列中

Question

My current table is fetching content from the database like this but I would like to combine the rows so that all the Room Preference's are displayed in one row rather than over several. How can I go about doing this? As a result the table below would have only two rows.

The Room Preference information is being inferred from ts_roompref.

Here is my code so far:

    $sql = "SELECT 
            *
    FROM ts_request
    INNER JOIN ts_day
    ON ts_request.day_id = ts_day.id
    INNER JOIN ts_period
    ON ts_request.period_id = ts_period.id
    INNER JOIN ts_allocation
    ON ts_request.id = ts_allocation.request_id
    INNER JOIN ts_roompref
    ON ts_request.id = ts_roompref.request_id
    WHERE ts_request.round=:round
    AND ts_request.dept_id=:dept
    ORDER BY ts_request.module_id ASC";
    }
    $stm = $pdo->prepare( $sql );
    $stm->execute( array( ':round' => 'P', ':dept' => $loggedin_id  ) );
    $rows = $stm->fetchAll();   

        foreach ($rows as $row) 
    {               
    echo '<tr align="center">'; 
    echo '<td>'.$row['module_id'].'</td>';
    echo '<td>'.$row['day'].'</td>';    
    echo '<td>'.$row['period'].'</td>';
    echo '<td>';
    if ($row['room_id']=="0")
    {
    echo "Any";
    }
    else
    {
    echo $row['room_id'];
    }
    echo '</td>';           
    echo '<td>'.$row['status'].'</td>';
    echo '</tr>';

Answer 1

Well, I guess your trying to do it in SQL, but if you wanted to do it in your PHP you could loop over the rows watching for changes to Module Code. Pseudo code something like...

$newrows = array()
$tmpCode = ""
foreach $rows as $row
   if $tmpCode != $row['module_code'] {
       $tmpCode = $row['module_code']

       $newrows[] = $row 
   } else {
       $newrows[count($newrows) - 1]['room_preference'] .= $row['room_preference']
   }
}

...rough, but you get the idea.