Yeah ... it ain't purdy, and it sure doesn't scale so you have to be careful where you use it.

This is usually more of a presentation issue that I'd suggest be left up to the application doing the display of the data.

As an example, I believe Microsoft Excel has a crosstab function that can query right from a database and do the 3D effect automagically.

If I wanted to do this regularly, I think I'd be inclined to write a job that finds the number of columns (or rows) and then runs a separate query for each of the respective rows (or columns). I was thinking through an HTML example, where it's difficult to add columns to a table on the fly, but having an "unknown" number of rows is no problem. I think I'd query the db to find how many columns there are (and the respective headings, in the example given, year numbers). Then, start a loop querying for each product and their respective annual sales and output each row.

Using this technique, you end up with a lot more queries back and forth to the server, but as an application, it could automatically scale in both width and height automatically.

-- Mitch

Chris wrote:
Mitch Miller wrote:

K.A.Bouton wrote:
 > I need the output of my sql to be a multidimensional array as follows.

then Chris wrote:
 > You won't be able to get an sql query to return in that format (I know
 > what you're trying to do, I've used the same chart software).

This is my MSSQL Server solution, and yep, it outputs EXACTLY like requested. I'm not sufficiently fluent with MySQL, so this may have some "dialect" translation that needs done.

Also, this IS NOT a scalable solution at all.  It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query.  Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).

I stand corrected :) mysql should support something like that but yeh it's not a great solution (ie it's a horrible query) ;)

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to