Row set Concatenation - SQL Server vs MySQL

Row set concatenation is a frequently required feature. Based on identical value, other unique values should be concatenated.

Consider the following set of data

create table test(id int, names varchar(100))
insert into test(id,names)
select 1,'Suresh' union all
select 1,'Kumar' union all
select 1,'Nithil' union all
select 2,'John' union all
select 2,'Murugan'

SQL Server

We can use FOR XML PATH in SQL Server as shown below

declare @names varchar(8000)
set @names=''
select distinct id,
    stuff((select (','+@names+names) from test as t2 where t1.id=t2.id for xml path('')),1,1,'') as names
from
    test as t1

The FOR XML PATH concatenates the names for each id and the STUFF function removes the first comma from a list

MySQL

MySQL has a built-in function named GROUP_CONCAT()

select id,group_concat(names) from test
group by id

This built-in function concatenates the names for each id. Simple!

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan