Re: How to convert rows into HTML columns?

am  Fri, dem 05.10.2007, um  8:20:32 +0200 mailte Stefan Schwarzer folgendes:
> Before I was used that the yearly values were all to be found in a
> single SQL row; now for each year of each country I have a separate
> row. How do I convert that into a single (HTML) row again?

You can use conditionals like in my simple example:

test=*# select * from vals;
 year |  val
------+--------
 1998 |  10.00
 1998 |  20.00
 1998 |  25.00
 1999 |  30.00
 1999 |  39.00
 2000 | 100.00
 2000 |  99.00
 2000 |  98.00
(8 rows)

test=*# select
  sum(case when year=1998 then val else 0 end) as "1998",
  sum(case when year=1999 then val else 0 end) as "1999",
  sum(case when year=2000 then val else 0 end) as "2000"
  from vals;
 1998  | 1999  |  2000
-------+-------+--------
 55.00 | 69.00 | 297.00
(1 row)


>
> And the next question coming up is: How should my query look like so
> that I can sort the (HTML) table by a specific year in ascending or
> descending order? So, that it doesn't display it by the country names
> alphabetical order, but by, say 1998?

If you have only one row, how would you sort this result? ;-)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net