Not possible. You can do a join, but you'll get more than just your list as the last
recordset. Just do a second query on the list part.
Travis
---- Original Message ----
From: Kay Smoljak <[EMAIL PROTECTED]>
Sent: 2002-08-21
To: SQL <[EMAIL PROTECTED]>
Subject: SQL function which returns a list
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
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
