manipulating table col

dcjones

Hi all,

I have a data table which holds the following format:

field: id
country:
countrycode:
rate;

Each record has a sister record which holds a different rate value, like:

id = 1, country=United States,countrycode=USA,rate1=2.7640
id = 2, country=United States,countrycode=USA,rate2=2.4568

I am trying to write a query that could produce a result like:

id = 1, country=United States,countrycode=USA,rate1=2.7640,rate2=2.4568

rate1 is from record is 1 and rate2 is from record id 2.

I have been trying all I know and have read most if not all of the posts relating to MySQL.

bradgrafelman

Can you show us more about how you're actually going to use this data? Rather than add extra columns, it's easier to retrieve the data in rows (since that's how it's stored). If nothing else, you can always retrieve all of the rates for a given countrycode (or country) by adding a GROUP BY clause and using GROUP_CONCAT().

It might just be easier, however, to use a GROUP BY clause and retrieve the rates one row at a time... it all depends on your application and what you're actually trying to do with the data.

dcjones

Hi Brad,

I get the data in a CSV file and becuase each country has a different Buy and Sell rate the buy and sell records are in separate rows.

The output needs to display both the buy ans sell rate in the same row so it looks like:

Country : Country Code : Buy rate : Sell rate

At the moment the output is displays like:

Country : Country code : Buy rate
Country : Country code : Sell rate
Country : Country code : Buy rate
Country : Country code : Sell rate
Country : Country code : Buy rate
Country : Country code : Sell rate

and so on.

I hope I have explained it a little more clearly.