Luis Cazares (5/5/2016)
Raghavendra Mudugal (5/5/2016)
Luis Cazares (5/4/2016)
Raghavendra Mudugal (5/4/2016)
(EP)Z!!!No need of STUFF. (very old code)
No need for cursors, or several statements.
WITH CTE AS(
select distinct personid from @t
)
SELECT PersonID,
phone = STUFF(( SELECT ', ' + AreaCode + '-' + Exchange + '-' + root
FROM @t t
WHERE t.PersonID = CTE.PersonID
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '')
FROM CTE;
If you use a Surround With snippet, the keystrokes are reduced by half while the performance improves.
Surround With snippet provided at: http://www.sqlservercentral.com/articles/SSMS/138994/
point of my code is only this- where without STUFF and by using ISNULL we can exclude the extra comma at the beginning.
@Pn = IsNull(@pn + ', ','') + (AreaCode+'-'+Exchange+'-'+ root)
I don't see any gain on that. You're replacing a function with another function. Not only that, your function would execute for every row instead of once per group. Your option won't guarantee the order of the items in the list, while the FOR XML option is able to do it. I understand that for SQL 2000 it was a good option (it would be better if it didn't use the default options of the cursor), but it's not worth it with the current tools available.
Actually, it can be very fast and it doesn't suffer the problem of returning multiple copies of a single row like XML can if you don't get the external reference just right. Also remember that the old method doesn't suffer the problem of entitizing reserved characters like XML does, the fix for which (using TYPE) causes XML to slow down a fair bit.
Both methods have their problems so I'd have to say what I always say... "It Depends". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.