Can I Comma Delimit Multiple Rows Into One Column?

sql, sql-server, sql-server-2008, t-sql

Solution

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)

Problem

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 ```

Original source

Related problems