How to Concatenate Multiple Rows into a Comma-Separated List in SQL Server and Oracle?

Aggregating Multiple SQL Rows into a Single Comma-Delimited Column

This article demonstrates how to consolidate multiple rows sharing a common identifier into a single row with a comma-separated list in a specific column. The example uses a table with ticket IDs and associated individuals, aiming to create a result where each ticket ID is linked to a list of all involved individuals.

SQL Server Approach (2005 and later)

The following SQL Server query achieves this using STUFF(), ISNULL(), and FOR XML PATH:

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

This query employs a subquery to gather individuals associated with each TicketID. The FOR XML PATH method transforms the result into a concatenated string, and STUFF() removes any leading comma. The ISNULL() function handles cases where no individuals are associated with a ticket. The optional columns provide flexibility in handling leading commas.

Oracle Solution

Oracle offers a more concise solution using the LISTAGG() function:

SELECT TicketID,
       LISTAGG(Person, ',') WITHIN GROUP (ORDER BY Person) OVER (PARTITION BY TicketID) AS People
  FROM @Tickets

LISTAGG() directly aggregates values into a comma-separated list, ordered by the Person column. The PARTITION BY clause ensures aggregation occurs for each distinct TicketID. This method is significantly simpler and more efficient than the SQL Server equivalent.

The above is the detailed content of How to Concatenate Multiple Rows into a Comma-Separated List in SQL Server and Oracle?. For more information, please follow other related articles on the PHP Chinese website!