Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 8BEB0475D70
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 14:19:02 -0400 (EDT)
Received: from l2.socialecology.com (unknown [4.42.179.131])
	by postgresql.org (Postfix) with ESMTP id 0F9B5475C98
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 14:19:02 -0400 (EDT)
Received: from 4.42.179.151 (broccoli.socialecology.com [4.42.179.151])
	by l2.socialecology.com (Postfix) with SMTP id C04F51F6DB5
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 11:19:01 -0700 (PDT)
X-Mailer: UserLand Frontier 8.0.5 (Macintosh OS) (mailServer v1.1..142)
Mime-Version: 1.0
Message-Id: <47562919.1176750558@[4.42.179.151]>
X-authenticated-sender: erics
In-reply-to: <20021023105925.U7340-100000@megazone23.bigpanda.com>
Date: Wed, 23 Oct 2002 11:18:58 -0700
To: pgsql-sql@postgresql.org
From: eric soroos <eric-psql@soroos.net>
Subject: Re: Hairy question - transpose columns
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/310
X-Sequence-Number: 10027

> 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

You could do the following:

 select cod_var, Year, Month, 1+((ten-1)*10) as Day, RainDay1 as Rain
  where RainDay1 is not null
 union all
 select cod_var, Year, Month, 2+((ten-1)*10) as Day, RainDay2 as Rain
  where RainDay2 is not null
..

 
I'm sure that there is a function that could do this too, but I'd tend to just convert the data and be done with it. 

eric