How to Concatenate Multiple Rows into a Single Comma-Delimited Column in SQL Server and Oracle?

Combining Multiple Rows into a Single Comma-Delimited Column (SQL Server and Oracle)

This guide demonstrates how to consolidate multiple rows based on a shared field (e.g., TicketID) and concatenate their corresponding values (e.g., Person) into a single comma-separated column (e.g., People). We'll explore solutions for both SQL Server and Oracle.

SQL Server Solution

SQL Server utilizes the STUFF function for efficient string concatenation and insertion. The following example illustrates this:

<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 Preceding Comma],
       ISNULL((
            SELECT ', ' + x.Person
            FROM @Tickets x
            WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
            FOR XML PATH (''), TYPE
        ).value('.','VARCHAR(max)'), '') [Preceding Comma If Not Empty]
FROM @Tickets t
GROUP BY t.TicketID</code>

Oracle Solution

Oracle offers the LISTAGG function, specifically designed for concatenating column values with a custom delimiter. Here's how it's used:

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

Both methods effectively merge multiple rows into a single column, separating the concatenated values with commas. The choice of function depends on your database system (SQL Server or Oracle).

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