Re: Hairy question - transpose columns

> 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