php + mysql将多行转换为单行中的列

Question

I have a following details in my database table with columns:

currency code
currency rate
currency trade type

Eg, I had the following rows in details (database):

USD  3.33  buy 
USD  3.43  sell
SGD  4.33  buy
SGD  4.43  sell

And then, I want to bring my database tables into PHP/HTML tables as following:

     BUY   SELL
USD  3.33  3.43 
SGD  4.33  4.43

How to use looping to make them be like that?

Answer 1

SELECT
    t1.code,
    t1.rate as buy,
    t2.rate as sell
FROM currency t1
JOIN currency t2 ON t2.code = t1.code AND t2.trade_type = 'sell'
WHERE t1.trade_type = 'buy'

Assuming this is a currency rate table and there are no duplicates for a any (code,trade_type) pair.