[thelist] MySQL to Excel Problem Pivot Table SOLVED!

[Jay Blanchard]

[snip]
SELECT RecordID, RecordDate, count(*) AS Quantity
FROM tblFOO
Group By RecordID, RecordDate

It returns;
+----------+------------+----------+
| RecordID | RecordDate | Quantity |
+----------+------------+----------+
| 100101   | 2002-03-21 |     6675 |
| 100101   | 2002-03-22 |     6794 |
| 100101   | 2002-03-23 |     2916 |
| 100101   | 2002-03-24 |     1215 |
| 100101   | 2002-03-25 |     6152 |
| 100101   | 2002-03-26 |     5398 |
+----------+------------+----------+

as expected (Only a subset here, there are hundreds of rows). I want to
place this data like this in an Excel table (bad ascii art example to
follow);

+------------+--------+--------+--------+
| RecordID   | 100101 | 100120 | 100131 |
+------------+--------+--------+--------+
| Date       |        |        |        |
+------------+--------+--------+--------+
| 2002-03-01 |        |     12 | 130101 |
+------------+--------+--------+--------+
| 2002-03-21 |   6675 |        |   1113 |
+------------+--------+--------+--------+
| 2002-03-22 |   6794 |    287 |  29045 |
+------------+--------+--------+--------+
| 2002-03-23 |   2916 |      6 | 233427 |
+------------+--------+--------+--------+

RecordID along the top row, dates down the side, quantities at the
intersection that they belong.
[/snip]

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
|
| 2001-11-02 |      4 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2001-11-05 |      1 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2001-11-06 |      4 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2001-11-07 |      3 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2001-11-09 |      1 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2002-03-01 |   4651 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2002-03-02 |  25070 |      0 |      0 |      0 |      0 |      0 |      0
|
| 2002-03-03 |      0 |      0 |      0 |      0 |  50165 |      0 |      0
|
| 2002-03-04 |      0 |      0 |      0 |      0 | 232125 |      0 |      0
|
| 2002-03-05 |      0 |      0 |      0 | 238226 |      0 |      0 |      0
|
| 2002-03-06 |      0 |      0 |      0 | 232864 |      0 |      0 |      0
|
| 2002-03-07 |      0 |      0 |      0 | 147123 |      0 |      0 |      0
|
| 2002-03-30 |      0 |      0 |      0 |  93806 |      0 |      0 |      0
|
| 2002-03-31 |      0 |      0 |      0 |  38022 |      0 |      0 |      0
|
| 2002-04-01 |      0 |      0 |      0 |      0 | 125322 |      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