This discussion is archived
4 Replies Latest reply: Nov 21, 2012 9:43 PM by 975606 RSS

parsing

975606 Newbie
Currently Being Moderated
Hi ,

I have a data as below , Need to parse the two columns together. The output i need is also showing . Please help

Id Description
35,42 agricultural product sales. (Id 35)
agricultural news and charts, market commentary, cash prices, government statistics and weather maps. (Id 42)
35,36,42 agricultural product sales1. (Id 35)
loan financing for agricultural producers. (Id 36)
crop planning. (Id 42)

Output shoulde be like

Id Description
35 agricultural product sales. (Id 35) , agricultural product sales1. (Id 35)
42 agricultural news and charts, market commentary, cash prices, government statistics and weather maps. (Id 42) ,crop planning. (Id 42)
36 loan financing for agricultural producers. (Id 36)

Regards

Edited by: 972603 on Nov 21, 2012 7:48 PM
  • 1. String Aggregation
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    That's called String Aggregation . This page shows several different ways to do it.
    Which way is best for you? That depends on your version of Oracle, and your exact requirements (e.g., is order important?)


     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: parsing
    jeneesh Guru
    Currently Being Moderated
    Like
    with sample_data as
    (
    select '35,42' id, 'agricultural product sales. (Id 35)
    agricultural news and charts, market commentary, cash prices, government statistics and weather maps. (Id 42)' description
    from dual union all
    select '35,36,42', 'agricultural product sales1. (Id 35)
    loan financing for agricultural producers. (Id 36)
    crop planning. (Id 42)' from dual
    ),
    split_data as
    (
    select regexp_substr(id,'[^,]+',1,level) id,
           regexp_substr(replace(description,chr(10)),'.*?\(Id \d+\)',1,level) description,
           row_number() over(partition by regexp_substr(id,'[^,]+',1,level) order by id desc) rn
    from sample_data
    connect by prior id = id
    and regexp_substr(id,'[^,]+',1,level) is not null
    and prior sys_guid() is not null
    )
    select id,replace(ltrim(max(sys_connect_by_path(description,'~')),'~'),'~',',') description
    --"or you could use listagg if you are in 11gR2"
    from split_data
    start with rn = 1
    connect by prior id = id
    and prior rn = rn -1
    group by id
    order by id;
  • 3. Re: parsing
    ranit B Expert
    Currently Being Moderated
    Hi,

    Welcome to the Forum!!!
    As Frank said, these are called String Aggregation and each technique is very much Database version dependent.
    /* Technique [1] */
    with xx as (
        select 35 id,'agricultural product sales.' dsc from dual union all
        select 42 id,'agricultural news and charts, market commentary, cash prices, government statistics and weather maps.' dsc from dual union all
        select 35 id,'agricultural product sales1.' dsc from dual union all
        select 36 id,'loan financing for agricultural producers.' dsc from dual union all
        select 42 id,'crop planning.' dsc from dual 
    )
    select id,wm_concat(dsc) from xx
    group by id;
    
    /* Technique [2] */
    with xx as (
        select 35 id,'agricultural product sales.' dsc from dual union all
        select 42 id,'agricultural news and charts, market commentary, cash prices, government statistics and weather maps.' dsc from dual union all
        select 35 id,'agricultural product sales1.' dsc from dual union all
        select 36 id,'loan financing for agricultural producers.' dsc from dual union all
        select 42 id,'crop planning.' dsc from dual 
    )
    SELECT id, RTRIM (XMLAGG (XMLELEMENT (e, dsc || ',')).EXTRACT ('//text()'), ',')
        FROM xx
    GROUP BY id;
    gives
    35     agricultural product sales.,agricultural product sales1.
    36     loan financing for agricultural producers.
    42     agricultural news and charts, market commentary, cash prices, government statistics and weather maps.,crop planning.
  • 4. Re: parsing
    975606 Newbie
    Currently Being Moderated
    Thanks Jeneesh , That was helpful.

Legend

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