How Can I Concatenate Column Values from Multiple Rows in Oracle SQL Using LISTAGG?

Oracle SQL: Combining Column Values Across Multiple Rows with LISTAGG

Oracle provides several ways to combine data from multiple rows into a single string. The LISTAGG function is a particularly effective method.

Imagine you have two tables: Table A (with a PID column containing values A, B, C) and Table B (with PID, SEQ, and Desc columns). The objective is to concatenate the Desc values from Table B, grouped by PID and ordered by SEQ, into a single Description column in the result set.

Here's how to accomplish this using LISTAGG:

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B
GROUP BY pid;

This query uses LISTAGG to aggregate the Desc values for each unique PID, ordering them by the SEQ column and using a space (' ') as the delimiter. The output will show each PID with its corresponding concatenated Desc values in the description column.

To include only the PIDs present in Table A, simply join this query with Table A:

SELECT a.pid, LISTAGG(b.Desc, ' ') WITHIN GROUP (ORDER BY b.seq) AS description
FROM A a
JOIN B b ON a.pid = b.pid
GROUP BY a.pid;

Important Consideration: LISTAGG typically functions correctly with VARCHAR2 data types.

The above is the detailed content of How Can I Concatenate Column Values from Multiple Rows in Oracle SQL Using LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!