Barney, that TSQL is brilliant. I've never used the Stuff function and had only used FOR XML PATH when generating xml. I had to go look at the books online and sure enough they have an example turning results into a value list in the same way that you are although they do the select as data(). I'm not familiar with the data() function and why they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of FOR XML PATH, the msdn reference is here: http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx Thanks, Judah On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert <bboisv...@gmail.com> wrote: > > Here you go. Note that they're not really semantically equivalent, > they just happen to return the same results. The approach for > obtaining the result is completely different, and the MS SQL one, > while less direct for the actual comma separate list creation, is far > more expressive for what you're actually trying to accomplish (i.e. > uses a correlated subquery instead of a grouped join). > > MySQL: > select name, group_concat(phone) as phones > from client > inner join phone on client.id = phone.clientId > group by name > > MS SQL Server: > select name, > stuff(( > select distinct ',' + phone > from phone > where clientId = client.id > for xml path ('') > ), 1, 1, '') as phones > from client > > cheers, > barneyb > > On Tue, Apr 14, 2009 at 12: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:321596 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
