This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Oct 5, 2012 12:50 PM by ranit B RSS

Concatenation rows string value into column

879994 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Did this get resolved?

    Ranit B.
  • 5. Re: Concatenation rows string value into column
    957206 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Check the FAQ - All the possible methods are discussed there - {message:id=9360005}
  • 13. Re: Concatenation rows string value into column
    Purvesh K Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points