MySQL Dynamic Cross Tab

Problem:

You have a table containing data with multiple dimensions, and you want to create a cross-tabulated table (pivot table) dynamically without knowing the number of dimensions upfront.

Solution:

The number of columns for a pivot query must be defined at query preparation time. To achieve dynamic cross-tabulation, you have two options that involve writing application code:

Option 1: Query Distinct Values and Construct Dynamic SQL

1. Query for distinct values in the dimension you want to pivot (e.g., "way").
2. Create an array of SQL fragments representing the maximum time for each distinct dimension value.
3. Join the SQL fragments into a single query string using commas.
4. Execute the dynamic pivot query with the constructed SQL string.

Example:

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`";

Option 2: Query and Post-Process Data Row by Row

1. Query the data as is.
2. Create an associative array using the first dimension value (e.g., "stop") as the key.
3. Populate the array with subarrays containing the second dimension value (e.g., "way") as keys and time values as values.
4. The resulting array will resemble the data from a pivot query.

Example:

$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"];
}