aggregate functions - MySQL dynamic cross tab

Question

I have a table like this:

way     stop    time
1       1       00:55
1       2       01:01
1       3       01:07
2       2       01:41
2       3       01:47
2       5       01:49
3       1       04:00
3       2       04:06
3       3       04:12

and I want a table like this:

stop    way_1   way_2   way_3   (way_n)
1       00:55           04:00
2       01:01   01:41   04:06
3       01:07   01:47   04:12
5               01:49

There are many solutions online about MySQL cross tab (pivot table), but how can I do this if I don't know how many "way" are there? Thanks

Answer 1

The number and names of columns must be fixed at the time you prepare the query. That's just the way SQL works.

So you have two choices of how to solve this. Both choices involve writing application code:

(1) Query the distinct values of way and then write code to use these to construct the pivot query, appending as many columns in the SELECT-list as the number of distinct values.

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

Now you can run the new query, and it has as many columns as the number of distinct way values.

$pivotstmt = $pdo->query($pivotsql);

(2) Query the data row by row as it is structured in your database, and then write code to pivot into columns before you display the data.

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

Now you have an array of arrays that looks the same as if you had run a pivot query, but the actual SQL you ran was a lot simpler. You post-processed the query result into a different set of arrays.