views:

936

answers:

3

Creating Comma Separated Lists In SQL


Hi All,

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
SELECT 'T0001', 'Alice' UNION ALL
SELECT 'T0001', 'Bob' UNION ALL
SELECT 'T0002', 'Catherine' UNION ALL
SELECT 'T0002', 'Doug' UNION ALL
SELECT 'T0003', 'Elaine'

SELECT * FROM @Tickets
+5  A: 

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)

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
John Gietzen
+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
tanging
@tanging: Awesome! I'm editing my response to reflect this!
John Gietzen
+10  A: 

SQL Server 2005+:

SELECT t.ticketid,
       STUFF(ISNULL(SELECT ', ' + x.people
                FROM TABLE x
               WHERE x.ticketid = t.ticketid
            GROUP BY x.people
             FOR XML PATH ('')), ''), 1, 2, '') [No Preceeding Comma]
       ISNULL((SELECT ', ' + x.people
                FROM TABLE x
               WHERE x.ticketid = t.ticketid
            GROUP BY x.people
             FOR XML PATH ('')), '') [Preceeding Comma If Not Empty]
  FROM TABLE t
GROUP BY t.ticketid

Reference:

OMG Ponies
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
Bad-ass! You are missing a group-by on the bottom, but awesome!
John Gietzen
@John: I am?! =)
OMG Ponies
@ OMG Ponies : great :)
Gaby
I'm curiuos: is this faster to run using a query on the server or to concatenate the results in memory?
llamaoo7
Cursors would be necessary to do that, and from what I have seen, they are EXTREMELY slow.
John Gietzen
+3  A: 

And, the MySQL version, for completeness:

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