Pivot Table with MySql

[v10]

I have a table something like this :
Date|Person|Item|Qty
13/8/2006|Jim|Apple|5
13/8/2006|Mary|Apple|2
13/8/2006|Paul|Pear|3
13/8/2006|Ann|Apple|9
13/8/2006|Louise|Pear|2
13/8/2006|James|Orange|6

and so on ...

The following query (from access) produces the result I want :
TRANSFORM Sum(Qty) AS SumOfQty
SELECT Person, Item, Sum(Qty) AS Totals
FROM TableName
WHERE (((Date)=#13/10/2006#))
GROUP BY Product, PIVOT Person;

Which is Items on vertical axis, Persons across horizontal axis with qty's below persons along corresponding Item

Can anyone shed some light on how I produce the same result from MySql ?

 

[TonyGroves]

Unfortunately, it can't be done; column names have to be defined in the table or query.

 

[rac2]

Pivot tables and crosstabs are not part of SQL, thats just the way it is.

But you can sometimes achieve this using self-joins. If the list of persons is known and stable then

SELECT a.ItemName, 
            SUM(ISNULL(b.Qty, 0, b.Qty) AS "Peter",
            SUM(ISNULL(c.Qty, 0, c.Qty) AS "Ann",
et cetera

FROM Items a
LEFT JOIN ItemsProduced b ON b.ItemID = a.ItemID
                                AND b.Person = 'Peter'
LEFT JOIN ItemsProduced c ON c.ItemID = a.ItemID
                                AND c.Person = 'Ann'
et cetera

GROUP BY a.ItemName

Notice that I have introduced a table of Items. This yields a row for every item whether or not any Person has produced it. The same consideration is addressed by the LEFT JOINs.

Also, I am not sure that ISNULL is a function in MySQL, but there is some equivalent.

If there are many Persons, then this is quite tedious. And if we do not know who the persons are when we write the query then the whole thing is impossible.