SQL Server / MySQL | Combine data from multiple rows into a single row

Author - Webner

We have data as below:

We need output like this:

Solution:
For SQL server
STUFF function is the key here:

SELECT Id,STUFF(
(
SELECT distinct '  ' + cast(Name as varchar(500))  from table1 t2  where t1.Id = t2.Id  FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,2,’ ‘
)
New_Name from table1 t1 group by Id,Name

MySql: In case of the MySQLGROUP_CONCAT function is used. Also, we can give the desired delimiter using SEPARATOR keyword:

SELECT id, GROUP_CONCAT(name SEPARATOR '  ') as New_Name FROM test GROUP BY id;
Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *