jagamot jagamot - 20 days ago 6
SQL Question

SQL Query to concatenate column values from multiple rows in Oracle

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

There are a few ways depending on what version you have

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

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

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

Comments