I don't know about the data function. I try to avoid SQL Server when I can. ;) We use it for some projects with MySQL's query language wasn't rich enough to express certain types of queries, but in general I use MySQL when possible.
Did they have an example of a query equivalent to mine that uses the data function? Because it seems like that'd probably be more efficient than going through an XML process. cheers, barneyb On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley <[email protected]> wrote: > > 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 <[email protected]> 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 >> <[email protected]> wrote: >>> >>> 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:321599 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
