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