SQL - convert data to a date/source/value "grid"

Question

I have data in an MYSQL database that looks like this:

Project    Date         Time
A          2009-01-01   15
A          2009-01-02   10
B          2009-01-02   30
A          2009-01-09   15
C          2009-01-07   5

I would like to produce output from this data like this:

Date         Project A Time     Project B Time    Project C Time
2009-01-01   15                 0                 0
2009-01-02   10                 30                0
2009-01-07   15                 0                 5

Can this be done with an SQL query, or do I need to write an external script to itterate through the DB and organize the output?

(Also, if someone has a better suggestion for a subject line let me know and I'll edit the question; I'm not sure of the proper terms to describe the current and desired formats, which makes searching for this information difficult)

Answer 1

You're looking for pivot / crosstab support. Here is a good link.

http://en.wikibooks.org/wiki/MySQL/Pivot_table

Answer 2

I believe this is called Pivot table. Just google for it.

Answer 3

I've been looking for something like this and found a MySQL stored procedure that works perfectly:

http://forums.mysql.com/read.php?98,7000,250306#msg-250306

The result you're looking for could be obtained from the following simple call:

call pivotwizard('date','project','time','from_table','where_clause')