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 Rainwhere Ten=1
union all
select cod_var, Year, Month, 2 as Day, RainDay2 as Rainwhere Ten=1
...
union all
select cod_var, Year, Month, 11 as Day, Rain Day 1 as Rainwhere Ten=2
...
union all
select cod_var, Year, Month, 29 as Day, RainDay9 as Rainwhere 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.