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

5 Answers 11

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
upvote
  flag
+1: Kudos for the Oracle example. There are a couple of ways to do it in Oracle, but some include using unsupported functionality. – OMG Ponies
1 upvote
  flag
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/… – Harrison
upvote
  flag
@tanging: Awesome! I'm editing my response to reflect this! – John Gietzen
up vote 69 down vote accepted

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:

1 upvote
  flag
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 .. – Gaby aka G. Petrioli
3 upvote
  flag
Bad-ass! You are missing a group-by on the bottom, but awesome! – John Gietzen
upvote
  flag
I'm curiuos: is this faster to run using a query on the server or to concatenate the results in memory? – llamaoo7
upvote
  flag
Cursors would be necessary to do that, and from what I have seen, they are EXTREMELY slow. – John Gietzen
upvote
  flag
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. – richard
upvote
  flag
+1 for name +1 for code – JPK

And, the MySQL version, for completeness:

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

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! */

upvote
  flag
This leaves a trailing comma. – John Gietzen
1 upvote
  flag
I took the liberty of making this match the example. This performs SIGNIFICANTLY faster than the previous best answer. – John Gietzen
1 upvote
  flag
@JohnGietzen the performance improvement does not come without a cost. This will not work as expected for values containing for instance a &. – Mikael Eriksson
upvote
  flag
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
upvote
  flag
Have a look at comments to this answer regarding distinct. //allinonescript.com/questions/9811577/… – Mikael Eriksson
upvote
  flag
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. – John Gietzen
upvote
  flag
I like that you took the time to keep working on it and posting your results. – JPK
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
upvote
  flag
In my scenario "Person" was of type Money, and this solution proved to be faster and more accurate than 'OMG Ponies' solution. – Omar
upvote
  flag
Confirmed that it works perfectly - thx a million! – wdanda

Not the answer you're looking for? Browse other questions tagged or ask your own question.