Date: 02/20/07 (PHP Community)    Keywords: mysql, sql

I have some MySQL tables. The relevant ones are:

COUNTRIES:
id, country

PRODUCTS:
prodcode, prodname, catid

STOCK:
id, prodcode, countryid, stockamt, lastupdate

At the moment, I am using the following query:
SELECT `stock`.`stockamt`, `products`.`prodcode`, `countries`.`country` FROM `stock` LEFT JOIN `products` ON `stock`.`prodcode` = `products`.`prodcode` LEFT JOIN `countries` ON `stock`.`countryid` = `countries`.`id`

And getting the following results:

Product Code	Stock Amount	Country
AMA-N-SW	324	UK
AMA-N-SW	546	Australia
AMA-N-SW	11	USA
BOOBS	123	UK

However, I would like to get something more like:

	UK	Australia	USA
AMA-N-SW	324	546	11
BOOBS	11	0	0

I have tried all sorts of GROUP BY queries, UNIONs, inserting the result into a multidimensional array and piecing it back together again and I just cannot figure out for the life of me how to make this work. Should I be working on my MySQL query, my presentational code, should I be changing the structure of my DB? Any help you can give would be wonderful.. and please try and make it relatively idiot proof as I've been on this for about 4 hours and my brains hurt. Thanks :)

Update: based on the awesome help I got I've found this which seems to be the most friendly explanation of what I want to do. Far too tired to do it now though, so I'll update again tomorrow with my solution (for anyone that is interested) :)

Source: http://community.livejournal.com/php/543919.html