SQL function which returns a list

[Kay Smoljak]

Hi all,

I'm wondering if this is possible in pure SQL - I'm using SQL Server
2000. I want to return a recordset, with one of the returned values
being a list which is actually pulled from a subquery. So if my tables
look like this:

table: product
productid
productname

table: region
regionid
regionname

table: regionlink
regionid
productid

I want to return a recordset that looks like this:

productid: 1
productname: Widget
regions: 1,2,5,8

I thought I could just do this:

SELECT productid, productname, 
        (SELECT region.regionid FROM region WHERE region.productid =
product.productid) AS list
FROM product 

with some kind of function around "list" to generate a comma-delimited
list, but I can't find any functions that do this.

So is this even possible? Or do I need to do some ugly CF ValueList()
stuff?

Thanks in advance,
Kay.

______________________________________________________
Kay Smoljak          Web Developer        PerthWeb Pty Ltd

Level 9/105 St George's Tc - Perth - Western Australia
Ph: (08) 9226 1366 Fax: (08) 9226 1375

www.perthweb.com.au          developer.perthweb.com.au

______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/sql@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists