September 10, 2012

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!

row-concat-sql


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

4 comments:

Alex Peta said...

In oracle there are a couple of ways to do this.

The simplest are:

WM_CONCAT() this is an undocumented function -- its the same as the mySQL equivalent.

The recomended official version is to use LISTAGG() function

ChrisM said...

thanks alex for an Oracle solution!

@madhivanan which SQL server version does this code run on. Anything new in SQL 2012 related to row concat?

Madhivanan said...

Thanks Alex Peta for your feedback

ChrisM, this code runs from version 2005 onwards. Nothing new related to row concatenation in version 2012

starsky51 said...

If you are able to deploy CLR functions on your instance, I highly recommend adding a concatenate function to your database. http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/

This would reduce your example down from:
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

to:
select id,
dbo.concat(names, ',') as names
from test
group by id

An added bonus is that it doesn't leave any trailing delimiters.