How to Create Dynamic Cross-Tab Queries in MySQL with Unknown Column Names?

Dynamic MySQL Cross Tab with Unknown Columns

In MySQL, traditional cross-tab queries require fixing the number and names of columns during query preparation. However, when the number of columns is unknown, as in the case of dynamic cross-tab queries, different approaches are required.

There are two primary solutions that involve writing application code:

1. Query Distinct Values and Construct the Query:

• Retrieve distinct values for the unknown column (e.g., way).
• Construct a pivot query by joining the distinct values to form the SELECT-list columns.

• Example:

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

2. Query Row-by-Row and Pivot in Code:

• Query the data in its original structure.
• Write code to pivot the data into columns before displaying it.

• 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>

By using either of these approaches, you can perform dynamic cross-tab queries in MySQL even when the number of columns is unknown, enabling flexibility and accommodating evolving data structures.

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