Displaying columns from 2 independant tables

I have a table as follows:
Code Quantity Status
ACC1 50 Dispatched
ACC2 31 Dispatched
ACC3 62 Dispatched
ACC4 11 Awaiting Dispatch
ACC5 13 Dispatched
ACC5 5 Awaiting Dispatch
ACC6 21 Awaiting Dispatch
I would like to sum the quantity of each code and staus
and display in tabular format as follows
Code Qty Dispatched Qty Awaiting Dispatch
ACC1 50 0
ACC2 31 0
ACC3 62 0
ACC4 0 11
ACC5 13 5
ACC6 0 21
Is this possible?
Thanks Wes
On Fri, 10 Dec 2004 09:55:41 -0800, Wes wrote:

>I have a table as follows:
(snip)
>I would like to sum the quantity of each code and staus
>and display in tabular format as follows
(snip)
Hi Wes,
If you can do the formatting at the client side (where it ought to be
done), use this:
SELECT Code, Status, SUM (Quantity)
FROM MyTable
GROUP BY Code, Status
If you must do the formatting at the server side:
SELECT Code,
SUM (CASE WHEN Status = 'Dispatched' THEN Quantity ELSE 0 END) AS
"Qty Dispatched",
SUM (CASE WHEN Status = 'Awaiting Dispatch' THEN Quantity ELSE 0
END) AS "Qty Awaiting Dispatch"
FROM MyTable
GROUP BY Code
(both queries untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)