MySQL query question, I can't get it

Question

I hope someone can help me out. I have a table that logs our import jobs. I need a query that will produce a matrix with the names of tables on the vertical axis, the import dates on the horizontal axis, and the total number of records imported for that table on that date in the matrix cell. I don't care if we have to create a temporary table, but the whole thing must be done in MySQL.

Below is a simplified sample of our event log table. Not only does it have many more fieds, but we import many more tables. Therefore, the solution should account for querying the table names. You will notice that data can be imported into a table more than once per day, as in records 5 and 6.

id  table_name  import_date          num_recs 
----+-----------+--------------------+------- 
0   customer    2010-06-20 00:00:00  10        
1   order       2010-06-20 00:00:00  15        
2   customer    2010-06-21 00:00:00  5         
3   order       2010-06-21 00:00:00  6         
4   customer    2010-06-22 00:00:00  1         
5   order       2010-06-22 00:00:00  6         
6   order       2010-06-22 00:00:00  1         

We are looking for a result something like this. It does not have to be exact

table_name  06-20 06-21 06-22
------------+-----+-----+------
customer    |  10 |   5 |   1
order       |  15 |   6 |   7

Answer 1

What about output of the form:

table_name   date    imports
------------+-------+--------
customer    | 06-20 |   10
customer    | 06-21 |   5
order       | 06-20 |   15
order       | 06-21 |   6

This way you can do it with a simple GROUP BY:

SELECT table_name, DATE(import_date) AS date, SUM(*) AS imports
FROM yourTable
GROUP BY table_name, date;

Otherwise, your query is going go be really nasty.

Answer 2

MySQL can not do pivot queries, but you can do it in two queries, using the result of the first query as the SQL for the next query:

SELECT 'SELECT table_name'
UNION
SELECT CONCAT(', SUM(IF(import_date = "',import_date,'", num_recs,0)) AS "',DATE_FORMAT(import_date, "%m-%d"),'"')
FROM event_log
GROUP BY import_date
UNION
SELECT 'FROM event_log GROUP BY table_name'

Then execute the output of that query to get your final results, e.g. for your example you would get:

SELECT table_name                                                           
, SUM(IF(import_date = "2010-06-20", num_recs,0)) AS "06-20"
, SUM(IF(import_date = "2010-06-21", num_recs,0)) AS "06-21"
, SUM(IF(import_date = "2010-06-22", num_recs,0)) AS "06-22"
FROM event_log GROUP BY table_name

You can either write a stored procedure to concatenate, prepare, and then execute the results of the first query, OR, if this is all run from a shell script, you can capture the results of the first query, then feed the results back into mysql.