Row Merging in Sql Server using ForXMLPath

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 272
Consider the situation where a user has multiple phone(s).

--Sample Data
DECLARE @T TABLE (ID INT, MobileNumber VARCHAR(30))
INSERT INTO @T VALUES
(1,'123456789'),(1,'987654321'),
(2,'345678912'),(2,'123458967'),(2,'567823409'),
(3,'234870345')
/* Output
----------------
ID MobileNumber
--- -------------
1 123456789
1 987654321
2 345678912
2 123458967
2 567823409
3 234870345
*/


The task is to merge the MobileNumbers as per the users

Query

SELECT t.ID
, STUFF(( SELECT ', ' + MobileNumber
FROM @T
WHERE ID = t.ID
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'') AS MobileNumbers
FROM @T t
GROUP BY t.ID

/*
Result
--------
ID MobileNumbers
--- ------------------
1 123456789, 987654321
2 345678912, 123458967, 567823409
3 234870345
*/

So are able to merge the phone(s) as per the users by using ForXMLPath .ForXMLPATH(https://msdn.microsoft.com/en-IN/library/ms178107.aspx ) was introduced in SQL Server 2005 mainly for Row Concatenation.

Comments or Responses

Login to post response