1 2 Previous Next 24 Replies Latest reply: Oct 5, 2012 2:50 PM by ranit B RSS

    Concatenation rows string value into column

    879994
      Hi Gurus,

      I have following sample data
         select 1111 id,'First row1111' entry_value from dual
         union all
         select 1111 id,'Second row1111' entry_value from dual
         union all
         select 1111 id,'Third row1111' entry_value from dual
         union all
         select 2222 id,'First row2222' entry_value from dual
      desired result
               ID               ENTRY_VALUE
       1111           First row1111 Second row1111 Third row1111
       2222           First row2222 
      Can anyone help ?

      Thanks in advance.
        • 1. Re: Concatenation rows string value into column
          user13117585
          If you are using 11g
          WITH t AS
          (
             select 1111 id,'First row1111' entry_value from dual
             union all
             select 1111 id,'Second row1111' entry_value from dual
             union all
             select 1111 id,'Third row1111' entry_value from dual
             union all
             select 2222 id,'First row2222' entry_value from dual
          ) 
          -- end sample data
          SELECT id, listagg(entry_value, ' ') WITHIN GROUP (ORDER BY id)
            FROM t 
           GROUP BY id;
          • 2. Re: Concatenation rows string value into column
            879994
            Sorry for not mentioning version. I am using oracle 10g.
            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
            PL/SQL Release 10.2.0.3.0 - Production
            CORE    10.2.0.3.0      Production
            TNS for Linux: Version 10.2.0.3.0 - Production
            NLSRTL Version 10.2.0.3.0 - Production
            Edited by: 876991 on Oct 4, 2012 10:56 AM
            • 3. Re: Concatenation rows string value into column
              ranit B
              This works awesome in all databases... TESTED.
              with xx as (
                 select 1111 id,'First row1111' entry_value from dual
                 union all
                 select 1111 id,'Second row1111' entry_value from dual
                 union all
                 select 1111 id,'Third row1111' entry_value from dual
                 union all
                 select 2222 id,'First row2222' entry_value from dual)
              select x.id,rtrim(XMLAGG(XMLELEMENT(e,x.entry_value||' ')).extract('//text()'),' ') from xx x
              group by x.id;
              gives
              1111     First row1111 Second row1111 Third row1111
              2222     First row2222
              HTH
              Ranit B.
              • 4. Re: Concatenation rows string value into column
                ranit B
                Did this get resolved?

                Ranit B.
                • 5. Re: Concatenation rows string value into column
                  957206
                  with TESTED as (
                  select 1111 id,'First row1111' entry_value from dual
                  union all
                  select 1111 id,'Second row1111' entry_value from dual
                  union all
                  select 1111 id,'Third row1111' entry_value from dual
                  union all
                  select 2222 id,'First row2222' entry_value from dual)
                  select T.id,WM_CONCAT(T.entry_value) COL from TESTED T
                  group by T.id;
                  • 6. Re: Concatenation rows string value into column
                    957206
                    with TESTED as (
                    select 1111 id,'First row1111' entry_value from dual
                    union all
                    select 1111 id,'Second row1111' entry_value from dual
                    union all
                    select 1111 id,'Third row1111' entry_value from dual
                    union all
                    select 2222 id,'First row2222' entry_value from dual)
                    select T.id,WM_CONCAT(T.entry_value) COL from TESTED T
                    group by T.id;
                    • 7. Re: Concatenation rows string value into column
                      957206
                      with TESTED as (
                      select 1111 id,'First row1111' entry_value from dual
                      union all
                      select 1111 id,'Second row1111' entry_value from dual
                      union all
                      select 1111 id,'Third row1111' entry_value from dual
                      union all
                      select 2222 id,'First row2222' entry_value from dual)
                      select T.id,WM_CONCAT(T.entry_value) COL from TESTED T
                      group by T.id;
                      • 8. Re: Concatenation rows string value into column
                        Ashu_Neo
                        You can do it by wm_concat or by a user defined function.
                        WITH data1 AS
                        (select 1111 id,'First row1111' entry_value from dual
                           union all
                           select 1111 id,'Second row1111' entry_value from dual
                           union all
                           select 1111 id,'Third row1111' entry_value from dual
                           union all
                           select 2222 id,'First row2222' entry_value from dual)
                         select id, replace(wm_concat(entry_value),',',' ') res
                         from data1
                         group by id
                        /
                        • 9. Re: Concatenation rows string value into column
                          957206
                          with TESTED as (
                          select 1111 id,'First row1111' entry_value from dual
                          union all
                          select 1111 id,'Second row1111' entry_value from dual
                          union all
                          select 1111 id,'Third row1111' entry_value from dual
                          union all
                          select 2222 id,'First row2222' entry_value from dual)
                          select T.id,WM_CONCAT(T.entry_value) COL from TESTED T
                          group by T.id;
                          • 10. Re: Concatenation rows string value into column
                            ranit B
                            I usually avoid wm_concat coz of below --

                            1. wm_concat is not documented.
                            2. It is not supported in older database's... i guess not in 10g also.
                            3. I'm not sure about this.. but.. can we have a string-separator of our choice while using wm_concat ?

                            Please rectify me if i'm wrong.
                            Ranit B.
                            • 11. Re: Concatenation rows string value into column
                              Purvesh K
                              Solution using SYS_CONNECT_BY_PATH:-
                              with data as
                              (
                                select 1111 id,'First row1111' entry_value from dual
                                 union all
                                 select 1111 id,'Second row1111' entry_value from dual
                                 union all
                                 select 1111 id,'Third row1111' entry_value from dual
                                 union all
                                 select 2222 id,'First row2222' entry_value from dual
                              )
                              select id, ltrim(max(sys_connect_by_path(entry_value, ', ')), ', ') agg
                                from (
                                      select id, entry_value,
                                             row_number() over (partition by id order by id) rn
                                        from data
                                     )
                              connect by id = prior id
                                     and prior rn = rn - 1
                               start with rn = 1
                               group by id;
                              
                              ID                     AGG    
                              ---------------------- ----------------------------------------------------
                              2222                   First row2222
                              1111                   First row1111, Second row1111, Third row1111  
                              • 12. Re: Concatenation rows string value into column
                                jeneesh
                                Check the FAQ - All the possible methods are discussed there - {message:id=9360005}
                                • 13. Re: Concatenation rows string value into column
                                  Purvesh K
                                  ranit B wrote:
                                  I usually avoid wm_concat coz of below --

                                  1. wm_concat is not documented.
                                  2. It is not supported in older database's... i guess not in 10g also.
                                  3. I'm not sure about this.. but.. can we have a string-separator of our choice while using wm_concat ?
                                  Yes, being non-Documented function, it should never be used. Especially when you have loads of alternatives available to achieve the task.

                                  The Article hosted at Oracle Base lists many such methods, String Aggregation
                                  • 14. Re: Concatenation rows string value into column
                                    BluShadow
                                    ranit B wrote:
                                    I usually avoid wm_concat coz of below --

                                    1. wm_concat is not documented.
                                    2. It is not supported in older database's... i guess not in 10g also.
                                    3. I'm not sure about this.. but.. can we have a string-separator of our choice while using wm_concat ?

                                    Please rectify me if i'm wrong.
                                    Ranit B.
                                    I would make No.1...

                                    1. Because Oracle does not recommend using it. (Tom Kyte being a representative of Oracle: Re: DISTINCT not working with  wmsys.wm_concat
                                    1 2 Previous Next