matrix display

[radujit]

Hi,
I have a tabel in access db fx(bank,currency,operation(buy or sell).
I created 4 recordsets that pick data as follows:
recordset1 (Bank, EUR, Buy)
recordset2 (Bank, EUR, Sell)
recordset3 (Bank, USD, Buy)
recordset4 (Bank,USD,Sell).

Now I want the data to be dispalyed using classic ASP on a page as follows:

Bank EUR-Buy EUR-Sell USD-Buy USD-Sell
recordset1 recordset1 recordset2 recordset3 recordset4

More specific:


Code:
in the original table in database:

ID Bank Transaction type Currency Value
1 X Buy EUR 1
2 X Sell EUR 2
3 X Buy USD 3
4 X Sell USD 4

Data in the ASP should be displayed as follows:



Bank EUR - Buy EUR - Sell USD-Buy USD-Sell
X 1 2 3 4


Thank you in advance.

[yamaharuss]

Will there ever be more records for each bank or will those 4 records be the only ones updated?

If just those 4 then this will work for each bank


SELECT
tbl.bank
,EURBUY = (select distinct value from fx where currency = 'EUR' and trans = 'buy' and bank = tbl.bank)
,EURSELL = (select distinct value from fx where currency = 'EUR' and trans = 'sell' and bank = tbl.bank)
,USDBUY = (select distinct value from fx where currency = 'USD' and trans = 'buy' and bank = tbl.bank)
,USDSELL = (select distinct value from fx where currency = 'USD' and trans = 'sell' and bank = tbl.bank)

FROM fx tbl
group by tbl.bank

[radujit]

Actually your solution is way much better, thank you.
Now I have some problems with the sql I cannot figure it out.So I would appreciate if could give me a little bit
of help.

More specific:

I have two tables
Banks(Bank_id, Bank)
FX (ID, Bk_id, currency, trans, value)

Banks and FX are innerjoined on Banks.Bank_id=FX.Bk_id.

Now here is the sql:


SELECT Bank_id,
(select distinct value from fx where currency = 'EUR' and trans = 'buy' and bk_id = Bank_id) as 'EUR - buy',
(select distinct value from fx where currency = 'EUR' and trans = 'sell' and bk_id = Bank_id) as 'EUR - sell',
(select distinct value from fx where currency = 'USD' and trans = 'buy' and bk_id = Bank_id) as 'USD - buy',
(select distinct value from fx where currency = 'USD' and trans = 'sell' and bk_id = Bank_id) as 'USD - sell'
FROM Banks
group by Bank_id
The error I get is "The subsql must contain at least one value.

Thank you again.

[yamaharuss]

You will need to use table names like I did.

It also helps (normalize) to have your bankids named the same

SELECT
bnk.bank
,EURBUY = (select distinct value from fx where currency = 'EUR' and trans = 'buy' and bankid = bnk.bankid)
,EURSELL = (select distinct value from fx where currency = 'EUR' and trans = 'sell' and bankid = bnk.bankid)
,USDBUY = (select distinct value from fx where currency = 'USD' and trans = 'buy' and bankid = bnk.bankid)
,USDSELL = (select distinct value from fx where currency = 'USD' and trans = 'sell' and bankid = bnk.bankid)

FROM banks bnk
inner join fx fx on fx.bankid = bnk.bankid
group by bnk.bank ,bnk.bankid