Linux Today: Linux News On Internet Time.
Search Linux Today
Linux News Sections:  Developer -  High Performance -  Infrastructure -  IT Management -  Security -  Storage -
Linux Today Navigation
LT Home
Contribute
Contribute
Link to Us
Linux Jobs


Top White Papers

More on LinuxToday


MySQL Prepared Statements to Generate Crosstab SQL

Mar 26, 2010, 17:04 (0 Talkback[s])
(Other stories by Rob Gravelle)

"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

Related Stories: