How to Dynamically Create Cross-Tabulations in MySQL with Varying Column Names?

MySQL Dynamic Cross Tabulation

Problem:

Consider a table containing attributes such as "way," "stop," and "time." The objective is to transform this data into a cross-tabulated format, where columns represent distinct "way" values, and rows represent "stop" values. Each cell contains the maximum "time" recorded for the corresponding "way" and "stop" combination.

Dynamic Solution:

MySQL's limitations dictate that column names and numbers must be fixed during query execution. To overcome this challenge, consider two application-based approaches:

Option 1: Query Distinct Values

• Retrieve distinct "way" values using a SELECT DISTINCT query.
• Construct a pivot query dynamically by appending the necessary MAX(IF()) clauses to the SELECT-list.
• Execute the generated pivot query to obtain the desired cross-tabulated result.

Option 2: Row-by-Row Processing

• Query the data row by row using a simple SELECT * query.
• Create an associative array that stores "stop" as the key and an array of "way" values and corresponding "time" values as the value.
• Process each row, populating the way_n values in the associated "stop" key's array.

Implementation:

For Option 1:

$way_array = [];
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`";
$pivotstmt = $pdo->query($pivotsql);

For Option 2:

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

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