[KLUG Members] Data transformation

[Robert G. Brown]

On 25 Sep 2005 19:11:06 -0400, bill wrote:
>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.
Sure, and maybe (with respect to the initial design and intentions) it is.

>> I have solved this problem in a number of environments, inclusing SQL-based
>> data systems....
>I'd agree with Bob here; which bugs me to no end :-).  
Yeah, amazing isn't it? I'm sure it'll be a challenge, but it happens! :)

>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.  
Well, yeah, except there are some useful generalities to be observed here.
I see the initial table as a transaction file, and now Andrew is being asked to 
produce columnwise (or feildwise) classificationss for some reason. Actually, it
would seem that the out is not normalized, I can more easily believe the input
may be normalized for the initial purpose of the table/database. However, SQL
doesn't handle this very well, but a program (and it could be PHP, but doesn't 
have to be) that calls a RDBMS/SQL interface layer may very well be the best
way to go. Other tasks for the non-SQL code would be SQL code generation and
managed execution. There are some data lanaguages that essentially abstract the 
problem into higher-level commands, and that's really helpful when such generality
is nessesary.
								Regards,
								---> RGB <---