Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A66E247623F for ; Wed, 23 Oct 2002 13:56:59 -0400 (EDT) Received: from natura.cebas.csic.es (natura.cebas.csic.es [161.111.146.9]) by postgresql.org (Postfix) with ESMTP id 7AF1A476185 for ; Wed, 23 Oct 2002 13:56:58 -0400 (EDT) Received: from natura.cebas.csic.es (localhost.localdomain [127.0.0.1]) by natura.cebas.csic.es (8.11.6/8.11.6) with ESMTP id g9NHrHr06363 for ; Wed, 23 Oct 2002 19:53:17 +0200 Received: from there (equipo97.cebas.csic.es [161.111.146.97]) by natura.cebas.csic.es (8.11.6/8.11.6) with SMTP id g9NHrHc06357 for ; Wed, 23 Oct 2002 19:53:17 +0200 Message-Id: <200210231753.g9NHrHc06357@natura.cebas.csic.es> Content-Type: text/plain; charset="iso-8859-15" From: andres javier garcia garcia To: PostgreSQL List Subject: Hairy question - transpose columns Date: Wed, 23 Oct 2002 19:56:34 +0200 X-Mailer: KMail [version 1.3.2] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/308 X-Sequence-Number: 10025 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