Re: How to convert rows into HTML columns?

am Fri, dem 05.10.2007, um 9:13:10 +0200 mailte Stefan Schwarzer folgendes:
> >>
> >>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? ;-)
>
> With the "old" design I had up to 240 rows - for each country/
> territory one row. Now, I have 240*num_years rows.

Maybe i don't understand you.
I extend the table:

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

Now, select for every country and every year all in one row (in the
example only for 1998 and country a and b, i'm to lazy):

test=*# select
sum(case when year=1998 and country='a' then val else 0 end) as "1998_a",
sum(case when year=1998 and country='b' then val else 0 end) as "1998_b",
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_a | 1998_b | 1999 | 2000
--------+--------+-------+--------
10.00 | 20.00 | 69.00 | 297.00
(1 row)

you can see: sort by year and country, and all in one line ;-)

Or, one row for every country, ordered by the country's name:

test=*# select country,
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
group by country
order by country;
country | 1998 | 1999 | 2000
---------+-------+-------+--------
a | 10.00 | 30.00 | 100.00
b | 20.00 | 39.00 | 99.00
c | 25.00 | 0 | 98.00
(3 rows)

Btw.: i'm subscribed on the list, please no extra CC to me.

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