1 2 Previous Next 18 Replies Latest reply: Oct 7, 2013 8:42 AM by BluShadow RSS

    need to fetch the data

    800849

      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

          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

            yes,

             

            i want to  appended commas to each item.

             

            Regards

            Das

            • 3. Re: need to fetch the data
              ranit B

              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

                i got the solution.

                 

                Regards

                Das

                • 5. Re: need to fetch the data
                  ranit B

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

                   

                  Thanks in advance.

                  • 6. Re: need to fetch the data
                    800849

                    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

                      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

                        Yes,

                         

                        How can i do that.

                         

                        Regards

                        Das

                        • 9. Re: need to fetch the data
                          Warren Tolentino

                          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

                            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

                              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

                                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

                                  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

                                     

                                    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