On Tue, Apr 14, 2009 at 3:47 PM, Barney Boisvert <bboisv...@gmail.com> wrote: > 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.
MSDN actually used almost exactly what you wrote. If you go to the MSDN page: http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx And look down for "C. Generating a value list using PATH mode" it shows their example. The only real difference is that they were using AS "data()" which I am not familiar with. It might be because the result returned from the subquery in their example is being put into a tsql variable and then used in another xml path query. Here is the subquery in their example: (SELECT ProductID as "data()" FROM Production.Product WHERE Production.Product.ProductModelID = Production.ProductModel.ProductModelID FOR XML PATH ('')) as "@ProductIDs" Cheers Judah > On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley <ju...@wiredotter.com> 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 <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:321600 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
