How can I represent a single row from result set as multiple rows ?

Question

Given for example a currency rates table with these columns (used 3 here, but in my situation there are about 30):

  date     | eur | usd | gbp
2010-01-28 | X   | Y   | Z

How do I convert it to this one (using row with the latest date):

currency | rate
eur      | X
usd      | Y
gbp      | Z

I've come up with a query like this:

SELECT 'eur' AS currency, eur AS rate FROM rates WHERE date = (SELECT MAX(date) FROM rates)
UNION
SELECT 'usd' AS currency, usd AS rate FROM rates WHERE date = (SELECT MAX(date) FROM rates)
UNION
...

It's huge and ugly. Are there other solutions ?

Answer 1

Sometimes the easiest solution (if you want nice-looking queries) is to re-engineer the schema. It may well be that the best solution is to change your table to be:

   date    | currency | rate
-----------+----------+-----
2010-01-28 |   eur    |  X
2010-01-28 |   usd    |  Y
2010-01-28 |   gbp    |  Z

with suitable indexes on date and currency for performance. That's the way it should be in 3NF since the rates depend on each other, violating the 3NF rule:

Every column must depend on the key, the whole key and nothing but the key, so help me Codd.

(I love that little ditty). Another alternative is to provide a view which does the same thing, then you query the view. It's no less work for the DBMS but your query at least looks prettier (the create view still looks ugly though).

Or you could just accept the fact that some queries look ugly, document it well, and move on :-)

Answer 2

EDIT: Forget this post. I just saw that you're using MySQL

You can use the UNPIVOT statement if you're using MS-SQL:

SELECT
      date,
      unpiv.ColumnName AS Currency, -- will contain 'eur', 'usd' and so on
      unpiv.ColumnValue AS Rate -- will contain the numeric values
 FROM
      /* your original FROM statement */
      Unpivot (ColumnValue FOR ColumnName in (eur, usd /* add more columns here */)) as unpiv

Answer 3

Do you have to do it in SQL?

This is quite trivial using a programming language.

In PHP:

$q = mysql_query("
  SELECT    eur, usd, gbp
  FROM      rates
  ORDER BY  date DESC 
  LIMIT 1
");

$table = false;
if($val = mysql_fetch_array($q, MYSQL_ASSOC))
{
  $table = array(
    'eur' => $val['eur'],
    'usd' => $val['usd'],
    'gbp' => $val['gbp'],
  );
}
echo "currency | rate\n";
echo "-----------------";
foreach($table as $cur => $rate)
  echo $curr."      | ".$rate."\n";