>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only