Create a comma separated field value

[BKQc]

I'm trying to build a SQL request that would search say for all companies. Each companies have many products. I know I could do a join but then, I have to loop trying to find when I'm changing record and moreover, my application need a comma separated list to work.

What I tried is :
select companyName, (Select ProductName FROM Product) as Products FROM Company
but I knew that it would not work......

Is there an aggregate function that allow to create comma separated lists in a field (I can't use stored procedures)

Thx a lot!

 

[blom0344]

Hello BKQc,

There is no standard aggregate function available in SQL that would the concatenation and store the result in a field. To do this you would need to store information temporarily untill a condition is met and than write the result to a field. SQL does not work this way. You have to resort to cursor based tools like PL/SQL or process the data first with , for instance VBA if you have no utilities for anything else..... T. Blom
Information analyst
tbl@shimano-eu.com