MySQL 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 online solutions for the MySQL cross tab (pivot table), but how can I do this if I don’t know how many paths there are? Thanks

Answer 1

The number and column names must be corrected during the preparation of the query. This is how SQL works.

So, you have two options for solving this problem. Both options include writing application code:

(1) Request various way values , and then write code to use them to build a composite query by adding as many columns to the SELECT list as the number of different 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 a new query and have as many columns as the number of different way values.

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

(2) Request a row of data by row because it is structured in your database, and then write code to rotate into columns before displaying 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 were doing a summary query, but the actual SQL you were running was a lot easier. You sent the processed request result to another set of arrays.