Re: Hairy question - transpose columns

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.