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

Concatenating Column Values from Multiple Rows in Oracle: A Comprehensive Solution

Oracle provides robust capabilities for data manipulation, including the ability to concatenate column values from multiple rows. Consider the following example, where we aim to combine the "Desc" values from table B for each unique "PID" value in table A.

Table A:

PID
A
B
C

Table B:

PID   SEQ    Desc
A     1      Have
A     2      a nice
A     3      day.
B     1      Nice Work.
C     1      Yes
C     2      we can
C     3      do
C     4      this work!

To achieve the desired output, where the "Desc" column in the output table is a concatenation of "Desc" values from table B for each "PID", we can employ the following SQL query:

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

This query leverages the LISTAGG function, which aggregates values from a specified column into a single string. By using the GROUP BY clause, we ensure that the values are grouped by the "PID" column. The ORDER BY clause further specifies the order in which the values are concatenated (in this case, by the "SEQ" column).

To complete the process, we can join the output of this query with table A to filter the "PID" values as required:

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

The resulting output will resemble the following:

PID   Desc
A     Have a nice day.
B     Nice Work.
C     Yes we can do this work!

With this approach, we effectively concatenate column values from multiple rows in Oracle, providing a powerful tool for data transformation and manipulation.

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