Re: transposed query?

Nick Barr wrote:
>>> 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

See the crosstab function in contrib/tablefunc. Here's what is looks
like given the above data:

select * from crosstab(
   'select thedate, id_punto, avg(flow) from muestras_rambla
    group by thedate, id_punto order by 1,2',
   'select distinct id_punto from muestras_rambla
    order by 1'
) as (thedate timestamp with time zone,
       c1 float8, c2 float8, c3 float8, c4 float8, c6 float8);

         thedate         |   c1    |   c2   |   c3   |   c4    |   c6
------------------------+---------+--------+--------+---------+--------
  2003-02-05 15:00:00-08 | 699.462 | 341.05 | 514.05 | 466.884 | 30.012
  2003-02-18 15:00:00-08 |  911.43 |        |        |         |
(2 rows)

This form of crosstab() requires 7.4RC1 or a 7.3 backpatched copy from here:
   http://www.joeconway.com/

HTH,

Joe