Concatenate results - grouped by Name

[flect]

Hi everyone.

i've got a seemingly simple function i'd like to perform with with a query but am unsure how to go about it.

Essentially I want to concatenate results of a query into a single field - grouped by distinct value.

eg - original table

NAME	VARIETY
Apples	Granny Smith
Apples	Golden Delicious
Apples	Red Delicious
Oranges	Blood
Oranges	Navel
Oranges	Valencia

I want to group by NAME, and have all varieties combined so the result would be:

NAME	VARIETIES
Apples	Granny Smith, Golden Delicious, Red Delicious
Oranges	Blood, Navel, Valencia

Any ideas? I can't seem to use GROUP BY as i'm not trying to aggregate anything.

 

[MStef]

Try to do this via CROSSTAB query.