Date: Wed, 23 Oct 2002 11:06:29 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
Cc: PostgreSQL List <pgsql-sql@postgresql.org>
Subject: Re: Hairy question - transpose columns
In-Reply-To: <200210231753.g9NHrHc06357@natura.cebas.csic.es>
Message-ID: <20021023105925.U7340-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 23 Oct 2002, andres javier garcia garcia wrote:

> Hello; I've got pluviometric data in a bizarre format (spanish
> administration is rather original) and I need to "transpose" them, to
> be able to use them as time series data for a model.
> As you may see, the date of a rain datum is defined by the value of
> the fields Year, Month,Ten (1=first ten days of month; 2=second ten
> days of month; 3=up to eleven last days of month). and the field in
> which the datum is (RainDay1, RainDay2...)
>
> This is no useful for me because I need something like:
>
>  cod_var |  Year  |  Month  |  Day  |      Rain     |
> --------------+-----------+------------+---------+----------------+--
>       452   | 1995   |  1          |   1     |       2         |
>       452   | 1995   |  1          |   2     |       5         |
>       452   | 1995   |  1          |   3     |       6         |
>       452   | 1995   |  1          |   4     |      -3         |
>       452   | 1995   |  1          |   5     |       0         |
>       452   | 1995   |  1          |   6     |       5         |
> ...................
>
> Perhaps this is not possible to do with a database? Should I manage to
> make a program to fix this?


I'm sure there's a better way, but I think a series of union alls would
do it but be rather computationally expensive.

select cod_var, Year, Month, 1 as Day, RainDay1 as Rain
 where Ten=1
union all
select cod_var, Year, Month, 2 as Day, RainDay2 as Rain
 where Ten=1
...
union all
select cod_var, Year, Month, 11 as Day, Rain Day 1 as Rain
 where Ten=2
...
union all
select cod_var, Year, Month, 29 as Day, RainDay9 as Rain
 where Ten=3 and RainDay29 is not null;
...

I'm uncertain what you do for days at the end of a month that
don't exist, I'm guessing they have nulls.

In 7.3, I'd say you might be able to make a function to do this
that returns a result set which would probably be much better.