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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
