Row Merging using COALESCE

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 337
Consider the below

--Sample Data
DECLARE @T TABLE (MobileNumber VARCHAR(30))
INSERT INTO @T VALUES
('123456789'),('987654321'),
('345678912'),('123458967'),('567823409'),
('234870345')

SELECT *
FROM @T

/* Result
-----------

MobileNumber
-----------------
123456789
987654321
345678912
123458967
567823409
234870345
*/


Our task is to merge all the MobileNumbers. We will accomplish this as under

--Query
DECLARE @MobileNumber VARCHAR(8000)
SELECT @MobileNumber = COALESCE(@MobileNumber + ', ', '') + MobileNumber
FROM @T
SELECT MergedRows = @MobileNumber

/*
MergedRows
----------------
123456789, 987654321, 345678912, 123458967, 567823409, 234870345
*/


COALESCE function - concatenate many rows into a single text string in SQL Server.More information (https://msdn.microsoft.com/en-IN/library/ms190349.aspx )

Comments or Responses

Login to post response