[KLUG Members] Data transformation

[bill]

On Sat, 2005-09-24 at 22:27, Robert G. Brown wrote:
> >On Fri, 2005-09-23 at 11:13 -0400, Andrew Eidson wrote:
> ....
> >> I am taking data from a SQL database that is long...
>  
> >> User_id Field_id Field_value
> >> 1        1         data1
> >> 1        2         data2
> ....
> >> 2        2         data2
> >> 2        3         data3
> ....
> >> What I need to do is export it but into a wide file so that it looks like
> >> the following
> >> 1, data1, data2, data3, data4
> >> 2, data1, data2, data3, data4

If this db were normalized it would be much easier.

> I have solved this problem in a number of environments, inclusing SQL-based
> data systems. The general solution can be quite efficient, but it is not a
> trivial task. One ends up generating SQL code to do a lot of the work, but
> a fair amount of stuff is best done in the body of the program calling the 
> DBMS layer.

I'd agree with Bob here; which bugs me to no end :-).  

The db isn't the place to do it (the db is already wonky).  Think of it
as if you were putting data in a HTML page.  Pull it out and extract the
data, then use your programming (PHP?) to put it back together however
you want.  

kind regards,

bill