Cursor?! Jeez. :-)
"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Deanna Schneider To: CF-Talk Sent: Tue Sep 12 20:52:06 2006 Subject: Creating comma-delimited list in SQL I know someone was just talking about this a day or so ago (or maybe even today), but I can't seem to find it. So, maybe whoever it is that wanted it will see this. This is Oracle-specific, but maybe you can translate it. You can make a function like so that will do what you want. I can't take much credit for this (other than figuring out that it left an open cursor and closing it). I swiped it off the web somewhere. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; close p_cursor; return l_result; end join; / Then, you can call it in a query like so. (This joins 2 tables and gets the corresponding ids from the secondary table.) select sku, name, price, description, categoryid, join(cursor(select categoryid from ces_pubs_overflow where sku = ces_pubs.sku)) categoryList from ces_pubs order by sku ; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
