Home >Database >Mysql Tutorial >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!