Fascinating. But how do you build the SELECT? Seems to me you would first have to "SELECT RecordID FROM tblClass10" and then script an iteration to build the whole series of IF clauses. Or did I miss something?
Andrew Hazen -----Original Message----- From: Gabriel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:16 PM To: Jay Blanchard; [EMAIL PROTECTED] Subject: Re: [thelist] MySQL to Excel Problem Pivot Table SOLVED! Ahh, the joy of cross-tabulation. Whee. :) Jay Blanchard wrote: > > Sorry for the cross-post, both lists contributed to a solution. Here is a > query (using MySQL 3.23) that will return cross tab, or pivot table > information; > > mysql> select RecordDate, > -> IF(RecordID='100101',count(*),0) AS "100101", > -> IF(RecordID='100118',count(*),0) AS "100118", > -> IF(RecordID='100119',count(*),0) AS "100119", > -> IF(RecordID='100131',count(*),0) AS "100131", > -> IF(RecordID='100132',count(*),0) AS "100132", > -> IF(RecordID='100135',count(*),0) AS "100135", > -> IF(RecordID='100137',count(*),0) AS "100137" > -> from tblClass10 > -> group by RecordDate; > > Returns; > +------------+--------+--------+--------+--------+--------+--------+---- ---- > + > | RecordDate | 100101 | 100118 | 100119 | 100131 | 100132 | 100135 | 100137 > | > +------------+--------+--------+--------+--------+--------+--------+---- ---- > + > | 2001-10-26 | 3 | 0 | 0 | 0 | 0 | 0 | 0 > | > | 2001-10-31 | 1 | 0 | 0 | 0 | 0 | 0 | 0 > > This table contains over 5 million records. Nothing needs to be done in PHP > except run this query and place the results. > > I feel like rudy today! :) > > Jay -- Gabriel Cain Unix Systems Administrator [EMAIL PROTECTED] Dialup USA, Inc. 888-460-2286 ext 208 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
