Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews <[email protected]> wrote: > > Nice. So group_concat works sort of like ColdFusion's valuelist method. Very > nice indeed! > > -----Original Message----- > From: Richard White [mailto:[email protected]] > 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:321589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
