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
at

4 comments:

  1. Alex PetaSeptember 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

    ReplyDelete
  2. ChrisMSeptember 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?

    ReplyDelete
  3. MadhivananSeptember 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

    ReplyDelete
  4. starsky51September 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.

    ReplyDelete