How to Dynamically Pivot Data in MySQL When Columns Are Unknown?

MySQL Dynamic Cross Tab

In scenarios where the number of columns in a cross-tab query is unknown, traditional MySQL cross-tab solutions may fall short. This article explores two approaches to tackle this challenge.

1. Dynamic Query Construction

This method involves fetching distinct values for the unknown column (e.g., way) and then dynamically constructing a query with the appropriate number of columns:

$pdo->query("SELECT DISTINCT `way` FROM `MyTable`");
foreach ($rows 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`";

This approach requires additional application code to construct the query dynamically, but it avoids complex SQL statements and is more flexible for handling a variable number of columns.

2. Row-by-Row Pivoting

Alternatively, you can query the data row by row and perform the pivoting in the application code:

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

This approach involves more post-processing in the application, but it allows for greater flexibility in how the data is pivoted.

The above is the detailed content of How to Dynamically Pivot Data in MySQL When Columns Are Unknown?. For more information, please follow other related articles on the PHP Chinese website!