At 04:05 AM 22/09/2005 +0000, you wrote:

>I appreciate any hint or help with this issue:
>
>I have Groups (of categories) table and Categories table, 1-->many;
>Group_id is foreign key in categories table. I need this result set:
>
>Group_id Groupname Categories
>1 Name1 Cat1;Cat2;cat5
>2 Name2 Cat6;Cat8...
>
>i.e. I want to have just one result field from Category in the form
>similar to TStringList.CommaText. Result set will be used in other
>JOIN statements.
>
>I am thinking about stored proc with cursor but cannot make anything...

That will be the only way to get this set, since relationships are between
single values in the corresponding table columns. However, there is no
relational way to link the derived "Categories" output above back to any
table, if that's what you need. You would have to parse that string in
your application - TStringList is the obvious way in Delphi - and pass the
extracted individual strings[i] values to a parameterised statement.

I think this design model is tortuous, actually. I'd much prefer to
implement the 1:M relationship directly in the GUI as master-detail.

However, getting your set via a selectable SP is easy enough - just have a
FOR SELECT ..INTO <varlist> loop for the group and nest another FOR SELECT
loop inside it, to pick up the category values according to the Group_id
variable's value and form them into that comma-separated string. Call
SUSPEND at the point where both the current iteration of the Group loop and
all of the iterations for the current Cat loop are finished.

./heLen