Re: transposed query? - Mailing list pgsql-general

Nick Barr wrote:

> javier garcia - CEBAS wrote:
>
>> 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
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
> How about:
>
>
> SELECT
>     date,
>     (SELECT s1.flow FROM muestras_rambla s1 WHERE s1.date=t1.date AND
> s1.id_punto=1) AS flow_1,
>     (SELECT s2.flow FROM muestras_rambla s2 WHERE s2.date=t1.date AND
> s1.id_punto=2) AS flow_2,
>     (SELECT s3.flow FROM muestras_rambla s3 WHERE s3.date=t1.date AND
> s1.id_punto=3) AS flow_3,
>     (SELECT s4.flow FROM muestras_rambla s4 WHERE s4.date=t1.date AND
> s1.id_punto=4) AS flow_4,
>     (SELECT s6.flow FROM muestras_rambla s6 WHERE s6.date=t1.date AND
> s1.id_punto=6) AS flow_6
> FROM
>     muestras_rambla t1
> GROUP BY
>     t1.date;
>
>
> This uses sub-queries in the Select clause to get the data in columns.
> There may be another way.
>
>
>
> Nick Barr
>

Ooops, having tested that query I found a typo (or four). Try:

SELECT
    date,
    (SELECT s1.flow FROM muestras_rambla s1 WHERE s1.date=t1.date AND
s1.id_punto=1) AS flow_1,
    (SELECT s2.flow FROM muestras_rambla s2 WHERE s2.date=t1.date AND
s2.id_punto=2) AS flow_2,
    (SELECT s3.flow FROM muestras_rambla s3 WHERE s3.date=t1.date AND
s3.id_punto=3) AS flow_3,
    (SELECT s4.flow FROM muestras_rambla s4 WHERE s4.date=t1.date AND
s4.id_punto=4) AS flow_4,
    (SELECT s6.flow FROM muestras_rambla s6 WHERE s6.date=t1.date AND
s6.id_punto=6) AS flow_6
FROM
    muestras_rambla t1
GROUP BY
    t1.date;



Nick Barr