create a matrix report with SQL challenging!

[penchal]

I have a table with three columns

Table Orders
(
id varchar2(64),
currency char(3),
cardname varchar(64))

I need a query which gives me the number of orders
for each currency and card in the following format.

cardname is in the row and currency is the column

------------------------ AUD(currency)--HKD------ MXP
American Express --------33--------------44--------- 12
Master Card --------------33------------- 31 --------22
Visa ----------------------44---------------12------- 23


I know I can write a query to get these numbers independently like

select cardname, count(id) from orders
where currency = 'AUD'

for each of the currency and get the report. But I was wondering if we can write a query to get the whole report in a single query by using decode or something..

Thanks in advance,

Penchal

[nealh]

How about:

select cardname, sum(decode(currency, 'AUD', 1, 0)) aud,
sum(decode(currency, 'HKD', 1, 0)) hkd, sum(decode(currency, 'MXP', 1, 0)) mxp
from orders
group by cardname