Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: How to Concatenate Query Output in a Nested Query

    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

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    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.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    In 10g, you can also use wm_concat. But you won't be able to specify the separator.

  4. #4
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    We are running Oracle 8.1.7.4.0
    The solution was from Ask Tom "Function to concatenate output"
    Thank you both!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •