Dynamic Cross Tabulation in MySQL

When dealing with a table with an unknown number of values in a particular column (like "way" in the example provided), creating a cross tabulation in MySQL can seem like a challenge. The conventional approach requires you to specify the column names at the time of querying, which may not be feasible in dynamic situations.

Solution Approaches

To tackle this challenge, two programmatic solutions can be employed:

1. Query Distinct Values and Construct Pivot Query

This approach involves fetching the distinct values of the column with uncertain counts. Using this list of values, you can construct a dynamic pivot query and append the necessary columns to your SELECT statement.

foreach ($pdo->query("SELECT DISTINCT `way` FROM `MyTable`") as $row) {
  $way = (int) $row["way"];
  $way_array[] = "MAX(IF(`way`=$way, `time`)) AS way_$way";
}
$pivotsql = "SELECT stop, " . join(", ", $way_array) . "FROM `MyTable` GROUP BY `stop`";

2. Query Row by Row and Pivot Post-Process

This alternative method involves querying the data row by row as it is structured in your database. You will collect the data in an array and then pivot it into columns before displaying it.

$stoparray = array();
foreach ($pdo->query("SELECT * FROM `MyTable`") as $row) {
  $stopkey = $row["stop"];
  if (!array_key_exists($stopkey, $stoparray)) {
    $stoparray[$stopkey] = array("stop"=>$stopkey);
  }
  $waykey = "way_" . $row["way"];
  $stoparray[$stopkey][$waykey] = $row["time"];
}

Both approaches require coding to adapt to dynamic situations where the number of columns is unknown. The choice of approach will depend on the specific data manipulation requirements and efficiency considerations.