6 Replies Latest reply: Aug 24, 2009 4:36 PM by scott_h RSS

    How to concatenate multiple row subquery into a single row

    scott_h
      We had been using the below statement in a report to check status dates for resumes. With more and more people applying for the same position, HR would like the report changed so that all dates for each status are printed on the same line/column in the output.
      SELECT
      .
      .
      .
      .
      select ias.status_change_date
           from per_assignment_status_types past,
                   irc_assignment_statuses ias
             where past.assignment_status_type_id = ias.assignment_status_type_id
             and ias.assignment_id = asg.assignment_id
             and past.user_status = 'Resume Reviewed'
             and rownum = 1 
      FROM
      .
      .
      WHERE
      .
      .
      Is there some way to modify the subqueries to that it will concatenate the rows when it returns multiple records or will I need to create a function that does this for me? I was trying to avoid creating a function as we would need to call it about 20 times per record. Thanks for your help.
        • 1. Re: How to concatenate multiple row subquery into a single row
          Frank Kulash
          Hi,

          That's called "String Aggregation"
          [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.

          The first way on that page, a user-defined agggregate function called STRAGG, is probably the easiest to use. You have to install the function (copy about 60 lines of code from that page and run it) once. After it's installed, you can use it in as many different queries as you want.


          On Oracle 10 (and up) you may have a similar function, WM_CONCAT (owned by WMSYS), already installed.
          WM_CONCAT is not documented, so you may not want to use it in your Production applications.

          STRAGG is not so convenient if the order of items in the concatenated string is important.
          In that case, use XMLAGG or SYS_CONNECT_BY_PATH, as shown later in the asktom page.
          MODEL can also do ordered string aggregation.
          • 2. Re: How to concatenate multiple row subquery into a single row
            scott_h
            Frank Kulash wrote:
            Hi,

            That's called "String Aggregation"
            [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.

            The first way on that page, a user-defined agggregate function called STRAGG, is probably the easiest to use. You have to install the function (copy about 60 lines of code from that page and run it) once. After it's installed, you can use it in as many different queries as you want.


            On Oracle 10 (and up) you may have a similar function, WM_CONCAT (owned by WMSYS), already installed.
            WM_CONCAT is not documented, so you may not want to use it in your Production applications.

            STRAGG is not so convenient if the order of items in the concatenated string is important.
            In that case, use XMLAGG or SYS_CONNECT_BY_PATH, as shown later in the asktom page.
            MODEL can also do ordered string aggregation.
            Thank you for your explanation. I will check out the Ask Tom page when available (getting a server down error) and will research the others that you mentioned. Thanks again.
            • 3. Re: How to concatenate multiple row subquery into a single row
              Hoek
              Hi,

              Asktom seems to be currently down for maintenance.
              However: if you do a search here on OTN on 'pivot' or 'rows into column', you'll find some great and useful examples.
              Also there's:
              http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
              • 4. Re: How to concatenate multiple row subquery into a single row
                scott_h
                Thanks to both of you for trying to steer me in the right direction. I have gone with one of the suggestions from the oracle_base site and was having difficulties integrating the solution to my query. The specific data I am looking to aggregate are status change dates. This is what I have so far:
                       (SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ias.status_change_date,','))
                               KEEP (DENSE_RANK LAST ORDER BY curr),',')
                       FROM   (SELECT ias.assignment_id,
                                      ias.status_change_date,
                                      ROW_NUMBER() OVER (PARTITION BY ias.assignment_id ORDER BY ias.status_change_date) AS curr,
                                      ROW_NUMBER() OVER (PARTITION BY ias.assignment_id ORDER BY ias.status_change_date) -1 AS prev
                               FROM   per_assignment_status_types past,
                                      irc_assignment_statuses ias
                               where  past.assignment_status_type_id = ias.assignment_status_type_id
                               and    ias.assignment_id = asg.assignment_id--------------------------------------------------------problem here
                               and    past.user_status = 'Active Application')
                        GROUP BY ias.assignment_id
                        CONNECT BY prev = PRIOR curr AND ias.assignment_id = PRIOR ias.assignment_id
                        START WITH curr = 1)
                Again, this is just a subquery in my main cursor. The problem with this is that it does not like the line indicated. ASG table is in the main query and I cannot reference it in this subquery (not sure why). Can someone tell me the correct syntax to integrate my query? Sorry, this one is a little over my head. Thanks.
                • 5. Re: How to concatenate multiple row subquery into a single row
                  Frank Kulash
                  Hi,

                  Unfortunately, you can't reference a table ( like asg) in a sub-query if it is not defined either in that sub-query, or in its super-query. It looks like asg was defined two (or more) queries up.

                  There are many ways to work around the problem. Which one is best for you depends on your tables and your data.

                  Whenever you have a question, it helps to post:
                  (1) The version of Oracle (and any other relevant software) you're using
                  (2) A little sample data (just enough to show what the problem is) from all the relevant tables
                  (3) The results you want from that data
                  (4) Your best attempt so far (formatted)
                  (5) The full error message (if any), including line number.

                  Executable SQL statements (like "CREATE TABLE AS ..." or "INSERT ..." statements) are best for (2).
                  Formatted tabular output is okay for (3).
                  • 6. Re: How to concatenate multiple row subquery into a single row
                    scott_h
                    Thanks for the heads up on the referenced table needing to be within one level, I was unaware of that restriction. The Ask Tom site is up again and his type/function solution to this is working perfectly for my needs. Thanks to everyone pointing me in the right direction.