[KLUG Members] Data transformation

[Robert G. Brown]

>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

Yes, this is a good and common problem. I think Andrew does not state the
general case. 

All you guys who answered:
Your replies are nice but represent very limited solutions to the problem.
The fact is that SQL, by itself, does not address this problem well at all.
A program written in a language that makes SQL calls is probably a lot more
adept at dealing with this problem, IN GENERAL, than any SQL package by 
itself.

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.

							Regards,
							---> RGB <---