Home >Database >Mysql Tutorial >How to Concatenate Multiple Rows into a Comma-Separated List in SQL Server and Oracle?

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

DDD
DDDOriginal
2025-01-21 06:42:12759browse

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:

<code class="language-sql">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</code>

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:

<code class="language-sql">SELECT TicketID,
       LISTAGG(Person, ',') WITHIN GROUP (ORDER BY Person) OVER (PARTITION BY TicketID) AS People
  FROM @Tickets</code>

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn