>I am trying to solve a problem using only SQL in VFP3.0b
>
>I have a table that is has the following structure:
>
>STAFFID
>LANGUAGE
>
>Which represents the languages that a staff member speaks. If a staff member speaks more than one language they have more than one record in the table.
>
>I would like to collapse this into one record for each staff member by putting all the languages that a staff memeber speaks into one field (for formatting purposes in a report). The structure of the new table thus becomes:
>
>STAFFID
>LANGUAGES
>
>The answer obviously uses a self join... but beyond this I am stumped.
>I don't care how many nested Selects or intermediate selects there are.
>
>If anyone has any time, I would greatly appreciate it.

Data in database
recno staffid language
1 aaa
2 robert english
3 robert french
4 sally french
5 sally spanish
6 grorge latin

PUBLIC clang,thisname
thisname = ''
clang = ''

SELECT staffid,max(cclang(lang.staffid,lang.language)) as languages;
FROM lang;
where recno() <> 1;
group by staffid

RELEASE clang,cname

FUNCTION cclang
parameter lcname,lcLanguage
IF lcname <> thisname
cLang = padr(lcLanguage,200)
else
cLang = padr(alltrim(cLang)+' '+lclanguage,200)
ENDIF
thisname = lcname
RETURN cLang

This is close to what you want. If you include record 1 and record 2 has the same staffid, you will get a double entry. So I put a dummy record for record 1. I didn't have time to figure a better sloution.

Hope this helps