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

score 77 · Accepted Answer · edited Mar 14 '13 at 20:07

77

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)

edited Mar 14 '13 at 20:07

John Gietzen

48,783
32
145
190

answered Jan 12 '10 at 01:23

OMG Ponies

325,700
82
523
502

1

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 .. – Gabriele Petrioli Jan 12 '10 at 02:04
3

Bad-ass! You are missing a group-by on the bottom, but awesome! – John Gietzen Jan 12 '10 at 02:10
I'm curiuos: is this faster to run using a query on the server or to concatenate the results in memory? – llamaoo7 Jan 12 '10 at 03:10
Cursors would be necessary to do that, and from what I have seen, they are EXTREMELY slow. – John Gietzen Jan 12 '10 at 14:11
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 Jan 28 '11 at 23:18
+1 for name +1 for code – JPK Jul 31 '14 at 09:05

score 13 · Answer 2 · answered Oct 04 '13 at 13:05

13

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

answered Oct 04 '13 at 13:05

satish

171
1
5

In my scenario "Person" was of type Money, and this solution proved to be faster and more accurate than 'OMG Ponies' solution. – Omar Jan 21 '14 at 22:16
Confirmed that it works perfectly - thx a million! – Wagner Danda da Silva Filho Jan 29 '14 at 15:26

score 13 · Answer 3 · answered Jan 12 '10 at 02:21

13

And, the MySQL version, for completeness:

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

answered Jan 12 '10 at 02:21

John Gietzen

48,783
32
145
190

score 11 · Answer 4 · edited May 23 '17 at 12:00

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

edited May 23 '17 at 12:00

Community

1
1

answered Jan 12 '10 at 01:11

John Gietzen

48,783
32
145
190

+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 Jan 12 '10 at 01:33
2

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): http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings & http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm – Harrison Jul 07 '10 at 19:13
@tanging: Awesome! I'm editing my response to reflect this! – John Gietzen Jul 07 '10 at 22:47
The correct link to the LISTAGG documentation https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm – Kai Jul 13 '18 at 15:50

score 10 · Answer 5 · edited Jul 09 '15 at 15:15

10

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

edited Jul 09 '15 at 15:15

davethecoder

3,856
4
35
66

answered Mar 13 '13 at 13:18

Nishad M

124
1
2

This leaves a trailing comma. – John Gietzen Mar 14 '13 at 19:58
1

I took the liberty of making this match the example. This performs SIGNIFICANTLY faster than the previous best answer. – John Gietzen Mar 14 '13 at 20:11
1

@JohnGietzen the performance improvement does not come without a cost. This will not work as expected for values containing for instance a `&`. – Mikael Eriksson Mar 14 '13 at 20:41
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 Mar 14 '13 at 20:46
Have a look at comments to this answer regarding distinct. http://stackoverflow.com/questions/9811577/summarize-the-list-into-a-comma-separated-string#comment12506393_9811631 – Mikael Eriksson Mar 14 '13 at 20:55
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 Mar 16 '13 at 15:41
I like that you took the time to keep working on it and posting your results. – JPK Jul 31 '14 at 09:08

Can I Comma Delimit Multiple Rows Into One Column?

5 Answers5

Linked

Related