SQL Query to concatenate column values from multiple rows in Oracle

Question

Would it be possible to construct SQL to concatenate column values from multiple rows?

The following is an example:

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!

Output of the SQL should be -

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

So basically the Desc column for out put table is a concatenation of the SEQ values from Table B?

Any help with the SQL?

Answer 1

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:

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

Then join to A to pick out the pids you want.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.