This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 7, 2013 6:42 AM by BluShadow RSS

need to fetch the data

800849 Newbie
Currently Being Moderated

Hi,

 

i have a table and the i want to fetch the data in the following way

 

 

test--table name

 

when we are fetching the data:- from the table test ,it is comig as bellow.

 

3P SUPPORT

ALLIED SVCS

AS-CDN

AS-DVSUP

BCS

BES

BMS

CAM

 

 

i want to fetch the data in the folllwoing way.

 

3P SUPPORT,

ALLIED SVCS,

AS-CDN,

AS-DVSUP,

BCS,

BMS,

CAM

 

Regards

Das.

  • 1. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    What are you trying to ask is not at all clear. Please demonstrate clearly.

     

    I can only see "BES" removed and commas appended to each item (at the end).

    But why was "BES" removed...? please explain.

  • 2. Re: need to fetch the data
    800849 Newbie
    Currently Being Moderated

    yes,

     

    i want to  appended commas to each item.

     

    Regards

    Das

  • 3. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    I asked for few more inputs:

    1. Why was "BES" removed?

    2. I can see the last record is not comma-appended. Is that expected?

    3. What is the requirement you are actually trying to solve through this?

     

    4. Are you really serious about the question you have asked?

  • 4. Re: need to fetch the data
    800849 Newbie
    Currently Being Moderated

    i got the solution.

     

    Regards

    Das

  • 5. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    Good to know that. Could you please share the solution with us?

     

    Thanks in advance.

  • 6. Re: need to fetch the data
    800849 Newbie
    Currently Being Moderated

    Hello,

     

    What exactly i want.

     

    Suppose for  a profile id 1 there are having  3 program like below.

     

    BCS

    BES

    BMS

     

    when i fetch the data from the select statemnet  the data should be look like below.

     

    profile id                      programs

    1                                   BCS,BES,COLL BMS

     

    Please help me to make the sql query.

     

    Regards

    Das

  • 7. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    I guessed... This looks like a case of String Aggregation.

     

    Something like this?

    (Only on versions > 11gR2)

    ranit@XE11GR2>> ed

    Wrote file afiedt.buf

     

      1  with test_table as

      2  (

      3  select 1 id, '3P SUPPORT' data from dual UNION ALL

      4  select 1 id, 'ALLIED SVCS' data from dual UNION ALL

      5  select 2 id, 'AS-CDN' data from dual UNION ALL

      6  select 2 id, 'AS-DVSUP' data from dual UNION ALL

      7  select 3 id, 'BCS' data from dual UNION ALL

      8  select 3 id, 'BES' data from dual UNION ALL

      9  select 3 id, 'BMS' data from dual UNION ALL

    10  select 3 id, 'CAM' data from dual

    11  )

    12  --

    13  -- Test data

    14  --

    15  select

    16     id,

    17     LISTAGG(data,',') WITHIN GROUP(order by data) data

    18  from test_table

    19* group by id


    ranit@XE11GR2>> /

     

      ID DATA

    ---- ----------------------------------------

       1 3P SUPPORT,ALLIED SVCS

       2 AS-CDN,AS-DVSUP

       3 BCS,BES,BMS,CAM

     

    The rows are grouped by column "Id" and String-aggregation is done for column "Data".

     

    Below solution works in all Oracle versions:

    ranit@XE11GR2>> ed

    Wrote file afiedt.buf

     

      1  with test_table as

      2  (

      3  select 1 id, '3P SUPPORT' data from dual UNION ALL

      4  select 1 id, 'ALLIED SVCS' data from dual UNION ALL

      5  select 2 id, 'AS-CDN' data from dual UNION ALL

      6  select 2 id, 'AS-DVSUP' data from dual UNION ALL

      7  select 3 id, 'BCS' data from dual UNION ALL

      8  select 3 id, 'BES' data from dual UNION ALL

      9  select 3 id, 'BMS' data from dual UNION ALL

    10  select 3 id, 'CAM' data from dual

    11  )

    12  --

    13  -- Test data

    14  --

    15  select

    16     id,

    17     RTRIM(

    18             XMLAgg(XMLElement(x,data||',')).extract('//text()')

    19             ,',') data

    20  from test_table

    21* group by id

    ranit@XE11GR2>> /

     

      ID DATA

    ---- ----------------------------------------

       1 3P SUPPORT,ALLIED SVCS

       2 AS-CDN,AS-DVSUP

       3 BCS,CAM,BMS,BES

     

     

    Read more on various String Aggregation techniks --

    1. http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

    2. listagg function in 11g release 2

  • 8. Re: need to fetch the data
    800849 Newbie
    Currently Being Moderated

    Yes,

     

    How can i do that.

     

    Regards

    Das

  • 9. Re: need to fetch the data
    Warren Tolentino Expert
    Currently Being Moderated

    800849 wrote:

     

    Hello,

     

    What exactly i want.

     

    Suppose for  a profile id 1 there are having  3 program like below.

     

    BCS

    BES

    BMS

     

    when i fetch the data from the select statemnet  the data should be look like below.

     

    profile id                      programs

    1                                   BCS,BES,COLL BMS

     

    Please help me to make the sql query.

     

    Regards

    Das

    that is more clearer than your original posting. if you posted that info earlier you could get the solutions that you are looking for right away.

     

    SQL> select * from pivot_tab;

          COL1 COL2
    ---------- -----
             1 a
             1 b
             1 c
             2 h
             2 h

    SQL> select p.col1,
      2         substr(max(substr(sys_connect_by_path (p.col2,','),2)),1,60)
      3         as col2
      4    from (select col1,
      5                 col2,
      6                 row_number() over (partition by col1 order by col1, col2) rn
      7            from pivot_tab) p
      8  start with p.rn = 1
      9  connect by p.rn = prior p.rn + 1
    10  and prior p.col1 = p.col1
    11  group by col1;

          COL1 COL2
    ---------- ------------------------------------------------------------
             1 a,b,c
             2 h,h

  • 10. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    Could you please explain what you are exactly trying to do with a CONNECT BY PRIOR in a simple case of String Aggregation?

     

    Check my post above... Do you find any anomaly over there?

    Please let me know, if any.

     

    -- Ranit

  • 11. Re: need to fetch the data
    APC Oracle ACE
    Currently Being Moderated

    There's more than one way to solve string aggregation questions.  I'm not sure that the CONNECT BY solution is any more obscure than your XML solution.

     

    Cheers, APC

  • 12. Re: need to fetch the data
    ranit B Expert
    Currently Being Moderated

    Hi APC,

     

    I prefer the XML technik of String aggregation. (not sure about other techniks but this doesn't have 4000 char length limitation)

     

    Atleast, it's not using {Analytic funcs + Connect-by-prior + sys_connect_by_path + MAX(SUBSTR) + group by}

     

    But after all, opinion differs!

     

    -- Ranit

  • 13. Re: need to fetch the data
    996599 Newbie
    Currently Being Moderated

    Hi,

       Please Try the below query and let me know your feedback.

     

          SQL> select profile_id, wm_concat(programs) from course_123 group by profile_id;

          

         Output:

                        PROFILE_ID             WM_CONCAT(PROGRAMS)   

                       --------------------           --------------------------------------------------   

                             1                           BCS,BES,BMS

                             2                           MS,MSC

     

     

     

     

  • 14. Re: need to fetch the data
    Rahul_India Journeyer
    Currently Being Moderated

     

    16     id,

    17     RTRIM(

    18             XMLAgg(XMLElement(x,data||',')).extract('//text()')

    19             ,',') data

     

    Can you explain this a bit ?

     

    Thanks,

    Rahul

1 2 Previous Next

Legend

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