How to Dynamically Create Cross-Tabulated Tables in MySQL?

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:

<code class="php">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`";</code>

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:

<code class="php">$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"];
}</code>

The above is the detailed content of How to Dynamically Create Cross-Tabulated Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!