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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "Row set Concatenation - SQL Server vs MySQL"
  1. Alex Peta said...
    September 10, 2012 at 2:51 AM

    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

  2. ChrisM said...
    September 10, 2012 at 4:12 AM

    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?

  3. Madhivanan said...
    September 10, 2012 at 4:26 AM

    Thanks Alex Peta for your feedback

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

  4. starsky51 said...
    September 10, 2012 at 5:56 AM

    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.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions