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.