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