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
Date: Wed, 23 Oct 2002 11:06:29 -0700 (PDT)
Message-ID: <20021023105925.U7340-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200210231753.g9NHrHc06357@natura.cebas.csic.es>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, andresjavier.garcia@wanadoo.es
Subject: Re: Hairy question - transpose columns
In-Reply-To: <20021023105925.U7340-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox