4 Replies Latest reply: Nov 21, 2012 11:43 PM by 975606 RSS

    parsing

    975606
      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
          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
            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
              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
                Thanks Jeneesh , That was helpful.