How to Concatenate Column Values from Multiple Rows in Oracle SQL?

Oracle SQL: Combining Column Values Across Multiple Rows

This guide demonstrates how to concatenate column values from multiple rows within an Oracle database using SQL. We'll explore efficient methods, including aggregate functions and the LISTAGG function.

One effective approach utilizes the LISTAGG function:

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

This query cleverly concatenates Desc values from TableB for each distinct PID, using a space as the separator. The output groups results by PID, providing a single concatenated string for each.

To integrate PID values from TableA, a join is necessary:

SELECT a.PID, b.description
FROM TableA a
INNER JOIN (SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM TableB
GROUP BY pid) b
ON a.PID = b.pid;

This joined query delivers the final result: each row displays the PID from TableA alongside its corresponding concatenated Desc string from TableB.

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