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:321593
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
