Hi,

I have two tables
Table A (columns p,q)
Table B (columns r,s)

I have a nested query like:

select q, (select s from B where B.r = A.p and rownum <2) from A

Is there a way to concatenate the results (like "row1 | row2 | row3 |" etc) of the nested query to one field if there are more than one records in the nested query so I can drop the rownum < 2 part?

Regards,

Wim Venema

Hi,

yes there are multiple ways, how to achieve this. They depend on Oracle version, you did not post.

In 11g, you may use LISTAGG function, as documented here (just follow the link for 11gR2 description).
In lower versions, you shall create auxiliary objects, like described on AskTom page here (using aggregates) or here (using function). Again, follow the links to get more details.

In 10g, you can also use wm_concat. But you won't be able to specify the separator.

We are running Oracle 8.1.7.4.0
The solution was from Ask Tom "Function to concatenate output"
Thank you both!!