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