Can I Comma Delimit Multiple Rows Into One Column?

Question

I am attempting to merge something like this in my SQL Server database:

[TicketID], [Person]
 T0001       Alice
 T0001       Bob
 T0002       Catherine
 T0002       Doug
 T0003       Elaine

Into this:

[TicketID], [People]
 T0001       Alice, Bob
 T0002       Catherine, Doug
 T0003       Elaine

I need to do this in both SQL Server and Oracle.

I have found the function GROUP_CONCAT for MySQL that does exactly what I need here, but MySQL is not an option here.

EDIT: Test bench:

DECLARE @Tickets TABLE (
    [TicketID] char(5) NOT NULL,
    [Person] nvarchar(15) NOT NULL
)

INSERT INTO @Tickets VALUES
    ('T0001', 'Alice'),
    ('T0001', 'Bob'),
    ('T0002', 'Catherine'),
    ('T0002', 'Doug'),
    ('T0003', 'Elaine')

SELECT * FROM @Tickets

Solution 1

Here is a solution that works in SQL Server 2005+:

SELECT t.TicketID,
       STUFF(ISNULL((SELECT ', ' + x.Person
                FROM @Tickets x
               WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') [No Preceeding Comma],
       ISNULL((SELECT ', ' + x.Person
                FROM @Tickets x
               WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), '') [Preceeding Comma If Not Empty]
  FROM @Tickets t
GROUP BY t.TicketID

Reference:

• STUFF (Transact-SQL)

Solution 2

DECLARE @Tickets TABLE (
    [TicketID] char(5) NOT NULL,
    [Person] nvarchar(15) NOT NULL
)
INSERT INTO @Tickets VALUES
    ('T0001', 'Alice'),
    ('T0001', 'Bob'),
    ('T0002', 'Catherine'),
    ('T0002', 'Doug'),
    ('T0003', 'Elaine')

SELECT * FROM @Tickets

Select [TicketID],
STUFF((SELECT ',' + Person FROM @Tickets WHERE (
TicketID=Result.TicketID) FOR XML PATH ('')),1,1,'') AS BATCHNOLIST
From @Tickets AS Result
GROUP BY TicketID

Solution 3

And, the MySQL version, for completeness:

select
    TicketId,
    GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People
from
    table
group by
    TicketId

Solution 4

I have found a way to do this in Oracle, but I still need to do it in SQL Server.

From http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings (Thanks tanging) (ORACLE 11 and up)

select
    TicketId,
    listagg(Person, ', ') People
from
    table
group by
    TicketId

From: http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html

with
    data
as
  (
    select
        TicketId,
        Person,
        ROW_NUMBER() over (partition by TicketId order by Person) "rownum",
        COUNT(*) over (partition by TicketId) "count"
    from
        Table
  )
select
    TicketId,
    LTRIM(sys_connect_by_path(Person,','),',') People
from
    data
where
    "rownum" = "count"
start with
    "rownum" = 1
connect by
    prior TicketId = TicketId
  and
    prior "rownum" = "rownum" - 1
order by
    TicketId

Solution 5

one example

SELECT DISTINCT
    t.TicketID,
    STUFF((SELECT ', ', i.Person as [text()]
           FROM @Tickets i 
           WHERE i.TicketID = t.TicketID
           FOR XML PATH ('')), 1, 2, '') as People
FROM
    @Tickets t

......... or try ..............

SELECT DISTINCT
    t.TicketID,
    STUFF((SELECT ', ' + i.Person    /* notice this line is different */
           FROM @Tickets i 
           WHERE i.TicketID = t.TicketID
           FOR XML PATH ('')), 1, 2, '') as People
FROM
    @Tickets t

/* this works when I used this for my table and credit goes to my manager that ROCKS! */

View more solutions

Comments

[OMG Ponies]

+1: Kudos for the Oracle example. There are a couple of ways to do it in Oracle, but some include using unsupported functionality.

[Gabriele Petrioli]

this will not work ... the grouping you do is not using the people field so it fails and xml path can not take a comma (,) as the identifier because it cannot create an element from it ..

[dhara tcrails]

Bad-ass! You are missing a group-by on the bottom, but awesome!

[llamaoo7]

I'm curiuos: is this faster to run using a query on the server or to concatenate the results in memory?

[dhara tcrails]

Cursors would be necessary to do that, and from what I have seen, they are EXTREMELY slow.

[Harrison]

I know this is old, but if you are using 11g you have ListAgg (which seems to be VERY similar to the MySQL Group_CONCAT): technology.amis.nl/blog/6118/… & download.oracle.com/docs/cd/E11882_01/server.112/e10592/…

[dhara tcrails]

@tanging: Awesome! I'm editing my response to reflect this!

[richard]

For what it's worth, I have had a similar need in the past, and tried many different methods, including writing my own CLR .NET aggregate function. By far, the XPATH solution that OMG Ponies has demonstrated is the fastest.

[dhara tcrails]

This leaves a trailing comma.

[dhara tcrails]

I took the liberty of making this match the example. This performs SIGNIFICANTLY faster than the previous best answer.

[Mikael Eriksson]

@JohnGietzen the performance improvement does not come without a cost. This will not work as expected for values containing for instance a &.

[Mikael Eriksson]

Using disntinct is also not the best way, If you test with some large amount of data you will see that a group by solution is faster.

[Mikael Eriksson]

Have a look at comments to this answer regarding distinct. stackoverflow.com/questions/9811577/…

[dhara tcrails]

Well, this is still a good answer, since it shows a couple of different techniques for using XML. I will go ahead an switch back to the original accepted answer.

[Omar]

In my scenario "Person" was of type Money, and this solution proved to be faster and more accurate than 'OMG Ponies' solution.

[Wagner Danda da Silva Filho]

Confirmed that it works perfectly - thx a million!

[JPK]

+1 for name +1 for code

[JPK]

I like that you took the time to keep working on it and posting your results.

[Kai]

The correct link to the LISTAGG documentation docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.‌​htm