>Hello Everybody
>
>I have a table/cursor "standardinfos" with default infos.(customer = 9911)
>I have another table/cursor "customerinfos" with variable special infos.
>The second table has columns fieldname,fieldvalue, linkkey
>When doing a left join from standardinfos to customer infos i get a result with 1 to n rows (4 rows in the following example)
>
>Now i need the records from table/cursor customerinfos in one record
>
>Fieldname Fieldvalue Linkkey
>F1 xyz 9911
>F2 123 9911
>F3 Blah 9911
>F4 4711 9911
>
>The wanted result should should look like:
>F1 F2 F3 F4 linkkey
>xyz 123 Blah 4711 9911
>
>Until now i select the customer standardinfo record into cursor a.
>Then i select the customerinfos into cursor b.
>Then i scan through cursor b and build an dynamic "create cursor" command.
>Create cursor c (linkkey,f1,f2,f3,f4)
>Than i do a second scan with append blank in c and replace c.linkkey with 9911,c.f1 with xyz,c.f2 with 123 ...
>At last i select the data from a and c to get a one record result cursor
>Looks stupid but works.
>
>Is there an SQL command to get the wanted result ???
>(Now i have the same data/table construction on a MySQL Webserver.)
>
>Any help welcomed
>Best regards
>Albert

In SQL Server there are ways to do this, but in 2000 you would need to use a UDF.

See discussion for SQL Server here
http://www.tek-tips.com/viewthread.cfm?qid=1556857

In VFP you can use UDF in SQL too, but it's not advisable, so your solution with scan is the best.