MySQL Prepared Statements to Generate Crosstab SQL

“MySQL Reporting requirements sometimes require both unknown
column and row values, necessitating a more powerful means of
generating crosstabs. Today’s article presents Prepared Statements,
which dynamically generate the SQL and assign it to a variable so
that we can tailor the output based on the number of data
values.

“During the past several weeks, we’ve been learning how to
create crosstabs in MySQL. We’ve now covered the basics of fetching
the row and column data, as well as how to overcome some of the
challenges inherent to outputting data as columns. As we saw,
organizing data into columns can be an arduous task due to the SQL
language’s natural tendency of appending data to rows. We can
transpose row data to columns easily enough, but the number of
possible data values in the horizontal axis need to be known before
hand.

“Unfortunately, there will be times that your reporting
requirements will require both unknown column and row values, or
have a tendency to change often enough to invalidate previous code.
In such instances, you need a more powerful means of generating
crosstabs. Today’s article presents just such a mechanism: Prepared
Statements. By dynamically generating the SQL and assigning it to a
variable, we can tailor the output based on the number of data
values, thus unburdening us from having to anticipate changes.”

Complete Story