sql query help me

[sreenivas jeenor]

I have a table structure

MemberDetails
----------------
MembershipDetailsID PK
MembershipID FK
Benefits Varchar

A MembershipID can contain many benefits which can be added.

At the time of retrieval i want a query to fetch all the benefits for this MembershipID in a single record.

i.e in normal suitations my query to fetch benefits is as following...

select Benefits from MemberDetails where MembershipID=10;
here i will get mutiple records.

what i want is a single record with Benefits values as comma seperated.



can you help me regarding this sub-query? is this posibble?is this question
against the normalisation rules?

Thanks
[ March 30, 2007: Message edited by: sreenivas jeenor ]

[Paul Sturrock]

Hmm. It doesn't sound like you need to so anything in SQL here. Why don't you handle this in whichever application generated the query? There is no easy way to turn a result set into a single record without some programming logic. You could do it in a Stored Procedure, by iterating though your results set to generate another one by concatenating the results.

[sreenivas jeenor]

Thanks Paul for your suggestion.