Put multiple results in one field

Hi all,

I need to put multiple results in one field, but not sure how. Here's
some sample code:

select a.name,
a.accountnum,
a.ordernum,
(select itemid from items where items_ordernum = a.ordernum) as
Items
>From clients a

There's multiple instances of 'itemid' in 'items' with the criteria I
gave, so this generates an error. I want to instead have the item
numbers listed as just a list seperated by commas, so the results might
be this:

Name AccountNum OrderNum Items
John Smith 12345 0000234 1233, 1333, 4322
Mike Jones 43223 0000023 4322, 543

And so forth. Is there someway to loop through this sub-query and make
the results become a list?

Thanks --

rlanglyHow do I concatenate strings from a column into a single row?
http://www.aspfaq.com/show.asp?id=2529

AMB

--
Message posted via http://www.sqlmonster.com|||Why do you wish to destroy First Normal Form? You are supposed to do
display formatting in the front end and never in the database.sql