2 Replies Latest reply: Dec 27, 2012 5:05 AM by fac586 RSS

    How to assign concatenated output to an item on page load

    tparvaiz
      Hi,

      I have a table that reads something like this

      City Route
      NewYork 2
      NewYork 4
      NewYork 5
      London A
      London B
      Paris X1


      I want to assign Routes (concatenated) to an item on page load... as an example for NewYork it should read like this

      P2_ROUTE = 2, 4, 5


      I am looking to do the above using query, something like this

      select (concatenated route)
      from Table_A
      where
      City = :P2_City

      any suggestions how to concatenate variables.?
        • 1. Re: How to assign concatenated output to an item on page load
          714270
          You can create a "Before Header" pl/sql process on the page as:
          begin
          
              for c1 in (
                              select route from table_a
                              where
                              city = :P2_CITY
                        ) loop
             
              :P2_ROUTE := :P2_ROUTE || c1.route ||  ', ';
              end loop;
              
              :P2_ROUTE := rtrim(:P2_ROUTE, ',');
              :P2_ROUTE := rtrim(:P2_ROUTE);
          
          end;
          You need to make sure that P2_CITY is set correctly when the above page process executes.
          • 2. Re: How to assign concatenated output to an item on page load
            fac586
            tparvaiz wrote:

            I am looking to do the above using query, something like this

            select (concatenated route)
            from Table_A
            where
            City = :P2_City

            any suggestions how to concatenate variables.?
            Yes. This is known as string aggregation, and there are many ways to do it. For example:
            /*
              Oracle 11.2; list length <= 4000 bytes
            */
            
            SQL> select
              2      l.city
              3    , listagg(d.department_id, ', ')
              4        within group (
              5          order by d.department_id) departments
              6  from
              7      locations l
              8        join departments d
              9          on l.location_id = d.location_id
             10  group by
             11*      l.city;
            
            CITY                      DEPARTMENTS
            ------------------------------ ----------------------------------------
            Bern                      240
            Bombay                      230
            Geneva                      90, 100, 110, 120
            Hiroshima                 170
            London                      40, 260
            Munich                      70
            Oxford                      80
            Seattle                  10, 30, 130, 140, 180, 190, 210, 250
            South Brunswick             150, 160
            South San Francisco            60
            Southlake                 50
            Stretford                 270
            Tokyo                      220
            Toronto                  20
            Utrecht                  200
            
            15 rows selected.
            
            /*
              Previous versions or 11.2 list length > 4000 bytes
            */
            
            SQL> select
              2      l.city
              3    , rtrim(
              4          xmlserialize(
              5           content
              6           xmlagg(
              7               xmlparse(content d.department_id || ', ')
              8               order by d.department_id))
              9        , ', ') departments
             10  from
             11      locations l
             12        join departments d
             13          on l.location_id = d.location_id
             14  group by
             15*      l.city;
            
            CITY                      DEPARTMENTS
            ------------------------------ ----------------------------------------
            Bern                      240
            Bombay                      230
            Geneva                      90, 100, 110, 120
            Hiroshima                 170
            London                      40, 260
            Munich                      70
            Oxford                      80
            Seattle                  10, 30, 130, 140, 180, 190, 210, 250
            South Brunswick             150, 160
            South San Francisco            60
            Southlake                 50
            Stretford                 270
            Tokyo                      220
            Toronto                  20
            Utrecht                  200
            
            15 rows selected.
            See Re: 4. How do I convert rows to columns? and here for more.