transposed query? - Mailing list pgsql-general

Hi;
I've got problems with a query. I'm not sure if it is possible to do this
with Postgres, although I think it should be.

I had resolved these kind of queryes in MSAccess, where they are called
"cross references querys" but I can't find my way in Postgres.

My table 'muestras_rambla' is like:
          date           | id_punto | muestra | flow  | n_nitrato ...
--------------------------+----------+---------+---------+-----------
 06/02/2003 00:00:00 CET  |        1 |       1 | 699.462 |     18.44
 06/02/2003 00:00:00 CET  |        1 |       2 | 699.462 |     13.79
 06/02/2003 00:00:00 CET  |        2 |       1 |  341.05 |     11.15
 06/02/2003 00:00:00 CET  |        2 |       2 |  341.05 |      17.3
 06/02/2003 00:00:00 CET  |        3 |       1 |  514.05 |     22.17
 06/02/2003 00:00:00 CET  |        3 |       2 |  514.05 |     16.62
 06/02/2003 00:00:00 CET  |        4 |       1 | 466.884 |     18.99
 06/02/2003 00:00:00 CET  |        4 |       2 | 466.884 |     19.02
 06/02/2003 00:00:00 CET  |        6 |       1 |  30.012 |      5.91
 06/02/2003 00:00:00 CET  |        6 |       2 |  30.012 |      4.15
 19/02/2003 00:00:00 CET  |        1 |       1 |  911.43 |     17.66
 19/02/2003 00:00:00 CET  |        1 |       2 |  911.43 |      9.23
...

where I 'id_punto' can take the values from 1 to 6, and muestra is 1 or 2.
Commonly, for each 'date' and 'id_punto' we have two samples (muestra), and
the 'flow' is also commonly the same for the two samples.

The case is that for every 'date' I want to have the 'time series' of flows
in the way:
date              1    2    3    4    6
03/10/2002    606.92    323.08    440.69    393.61    5.49
17/10/2002    348.19    400.32    319.33    211.26    2.53
29/10/2002    411.57    409.66    310.37    362.20    9.36
14/11/2002    446.96    373.72    302.79    348.69    5.18
26/11/2002    381.20    386.85    307.77    240.75    6.41
12/12/2002    416.84    307.03    317.94    249.36    5.64
26/12/2002    743.04    380.30    539.94    521.60    3.69
09/01/2003    403.07    301.16    370.30    379.41    3.69
...

where the names of the rows are the values in 'id_punto' and the values of
the data are the average (for every 'id_punto' and 'date') between the two
samples (muestra).

In MSaccess this is solve with:
TRANSFORM avg(muestras_rambla.flow) AS avg_flow
SELECT muestras_rambla.date
FROM muestras_rambla
GROUP BY muestras_rambla.date
 PIVOT muestras_rambla.id_punto;

All I can do in postgres is :
--------
SELECT muestras_rambla.fecha, id_punto, avg(caudal) AS avg_caudal
FROM muestras_rambla
GROUP BY fecha, id_punto
--------
to get:
          fecha           | id_punto |    avg_caudal
--------------------------+----------+------------------
 03/10/2002 00:00:00 CEST |        1 | 606.924
 03/10/2002 00:00:00 CEST |        2 | 323.079
 03/10/2002 00:00:00 CEST |        3 | 440.690
...
But, how could  manage this to get the 'time series' table?

Thanks for your help and best wishes

Javier