Here is an Oracle UDF (for lack of a better description) that I just found.
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; return l_result; end join; Syntax: join(cursor(select name form users)). If you eant to change the default delim (which is a comma) you would do this: join(cursor(select name form users), '|') On Tue, Apr 14, 2009 at 3:31 PM, C. Hatton Humphrey <chumph...@gmail.com>wrote: > > Is there a MSSQL version of that puppy? > > On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews <li...@commadelimited.com> > wrote: > > > > Nice. So group_concat works sort of like ColdFusion's valuelist method. > Very > > nice indeed! > > > > -----Original Message----- > > From: Richard White [mailto:rich...@j7is.co.uk] > > Sent: Tuesday, April 14, 2009 2:59 PM > > To: cf-talk > > Subject: Re: expert sql challenge > > > > > > thanks for all your replies. > > > > barney, yes we are using mysql and didnt even know about the > qroup_concat. > > it works like a dream and where this task was taking 23 seconds to > complete > > it is now taking a matter of milliseconds :) > > > > fantastic and thanks once again for all your replies :) > > > >>What DB are you using? If it's MySQL, you can use this: > >> > >>select client.name, group_concat(clientphone.number) as phone numbers > >>from client > >> left outer join clientphone on client.id = clientphone.clientId group > >>by client.name > >> > >>If it's MS SQL Server you can use a subquery with FOR XML PATH and > >>STUFF to approximate the same functionality. Don't know about other > >>platforms. > >> > >>At the very least, pull a single recordset with the same JOIN as above, > >>but no GROUP BY, and then you can do the "rollup" in a single CFOUTPUT > >>loop. That'll save you a lot of trips to the DB, and therefor a lot of > >>wasted time. > >> > >>cheers, > >>barneyb > >> > >> > >>> > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
