From: andres javier garcia garcia <andresjavier.garcia@wanadoo.es>
To: PostgreSQL List <pgsql-sql@postgresql.org>
Subject: Hairy question - transpose columns
Date: Wed, 23 Oct 2002 19:56:34 +0200
Message-ID: <200210231753.g9NHrHc06357@natura.cebas.csic.es> (raw)
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.
The original data are in a format like
cod_var | Year | Month | Ten | RainDay1 | RainDay2 | RainDay3 | Rainday4 | Rainday5 | RainDay6 | RainDay7 | RainDay8 | Rainday9 | Rainday10 | Rainday11
--------------+-----------+------------+---------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
452 | 1995 | 1 | 1 | 2 | 5 | 6 | -3 | 0 | 5 | 4 | 5 | 4 | 4 |
452 | 1995 | 3 | 2 | 4 | 5 | 0 | 5 | 3 | 23 | 4 | 34 | 4 | 2 |
452 | 1996 | 12 | 3 | 12 | 2 | 3 | 4 | 7 | 3 | 3 | 15 | 2 | 4 | 3
452 | 1998 | 9 | 2 | 2 | 8 | 6 | -3 | 5 | 0 | 2 | 6 | 0 | 1 |
452 | 1998 | 3 | 3 | 2 | -3 | 7 | 9 | 4 | 2 | 5 | 6 | 1 | 16 | 3
......................
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?
Thanks for your help. I really can't imagine how to do this with Postresql; though I'm a newbye.
Regards
-----------
Javier
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: andresjavier.garcia@wanadoo.es
Subject: Re: Hairy question - transpose columns
In-Reply-To: <200210231753.g9NHrHc06357@natura.cebas.csic.es>
* 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