MySQL dynamic cross tab

by

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.

More Related Contents:

  1. MySQL – Rows to Columns
  2. SQL select only rows with max value on a column [duplicate]
  3. SELECTING with multiple WHERE conditions on same column
  4. mysql select dynamic row values as column names, another column as value
  5. MySQL “Group By” and “Order By”
  6. Is there ANY_VALUE capability for mysql 5.6?
  7. Aggregate function in MySQL – list (like LISTAGG in Oracle)
  8. Mysql query to dynamically convert rows to columns on the basis of two columns
  9. MySQL dynamic pivot table
  10. SQL Query to get column values that correspond with MAX value of another column?
  11. How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?
  12. use mysql SUM() in a WHERE clause
  13. Row to column transformation in MySQL
  14. How to select data where a field has a min value in MySQL?
  15. mysql count group by having
  16. Reorder / reset auto increment primary key
  17. Required to join 2 tables with their FKs in a 3rd table
  18. Setting the MySQL root user password on OS X
  19. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  20. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  21. How to auto generate migrations with Sequelize CLI from Sequelize models?
  22. Getting all parent rows in one SQL query
  23. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  24. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  25. mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists
  26. How to retrieve JSON data from MySQL?
  27. Is incrementing a field in MySQL atomic?
  28. Delete from one table with join
  29. MySQL: Compare BETWEEN time
  30. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

Leave a Comment