converting rows to columns

[AOLBoy]

My SQL exposure is relatively basic. I was wondering if it is possible to build a command that can retrieve data from a table into new columns.

I have a table with the following columns:

AccountNo
Ccy
Amount

where the Ccy is always either GBP or USD.

I want to create an SQL statement that can return me a new table as follows:

AccountNo
AmountGBP
AmountUSD

Can this be done with one command?

 

[piti]

as mysql does not support SELECT ... INTO statement with creation of new table you have to use the CREATE ... SELECT statement

e.g.

CREATE TABLE NewTable (AccountNo int, AmountGBP decimal, AmountUSD decimal) SELECT AccountNo, CASE WHEN Ccy = 'GBP' THEN Amount ELSE NULL END, CASE WHEN Ccy = 'USD' THEN Amount ELSE NULL END FROM OldTable