10 Replies Latest reply: Jan 15, 2013 2:43 AM by BluShadow RSS

    Print Data with Comma Seprated Format.

    Sudhir_Meru
      Hi,

      I need to prepare a report with column values as comma separated format. I am using >>oracle 11g express edition

      Table has employee names and number of client he attended the interview, It is stored in the below format.
      emp_name client_name
      JAMES A
      JAMES B
      JAMES C
      ALEX A
      ALEX D
      SCOTT B
      SCOTT C
      My Requirement for the report is shown below
      emp_name count(client_name) client_name
      JAMES 3 A,B,C
      ALEX 2 A,D
      SCOTT 2 B,C
      Please advise me is there any function which converts and print in comma separated format.


      Thanks
      Sudhir
        • 1. Re: Print Data with Comma Seprated Format.
          hitgon
          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:419593546543

          Edited by: hitgon on Dec 7, 2012 6:39 PM
          • 2. Re: Print Data with Comma Seprated Format.
            hitgon
            http://www.dba-oracle.com/t_converting_rows_columns.htm
            • 3. Re: Print Data with Comma Seprated Format.
              BluShadow
              Sudhir_Meru wrote:
              Hi,

              I need to prepare a report with column values as comma separated format. I am using >>oracle 11g express edition

              Table has employee names and number of client he attended the interview, It is stored in the below format.
              emp_name client_name
              JAMES A
              JAMES B
              JAMES C
              ALEX A
              ALEX D
              SCOTT B
              SCOTT C
              My Requirement for the report is shown below
              emp_name count(client_name) client_name
              JAMES 3 A,B,C
              ALEX 2 A,D
              SCOTT 2 B,C
              Please advise me is there any function which converts and print in comma separated format.


              Thanks
              Sudhir
              That's a form of pivoting, with string aggregation.

              Please read the FAQ: {message:id=9360005}
              • 4. Re: Print Data with Comma Seprated Format.
                Veejays.User10302525-Oracle
                If you are on 11g R2 you could use below sort of query, this is on emp table in scott schema.
                select mgr,count(*),listagg(ename,',') within group(order by mgr) from emp group by mgr;
                
                
                7566|2|FORD,SCOTT
                7698|5|ALLEN,JAMES,MARTIN,TURNER,WARD
                7788|1|ADAMS
                7839|3|BLAKE,CLARK,JONES
                7902|1|SMITH
                7934|1|AMIT
                • 5. Re: Print Data with Comma Seprated Format.
                  ranit B
                  try this...

                  /* code removed after suggested by BluShadow */

                  I'll post other String-Aggregation methods...
                  try this...
                  select 
                      emp_name, 
                      count(client_name) cnt,
                      rtrim(XMLAGG(xmlelement(e, client_name||',')).extract('//text()'),',') 
                  from xx
                  group by emp_name
                  order by cnt desc;
                  For versions > 11g Release-2
                  select 
                      emp_name, 
                      count(*) cnt, 
                      LISTAGG(client_name,',') WITHIN GROUP(ORDER BY NULL) 
                  from xx
                  group by emp_name;
                  Edited by: ranit B on Dec 7, 2012 6:50 PM
                  -- added code
                  • 6. Re: Print Data with Comma Seprated Format.
                    BluShadow
                    ranit B wrote:
                    But WM_CONCAT is not advisable
                    So why did you mention it? Why recommend someone use a function that will render their application unsupported by Oracle, or that may change it's functionality or even be removed by Oracle in future releases of the database?

                    Better to not mention it at all, rather than mislead people to think they can use it.
                    • 7. Re: Print Data with Comma Seprated Format.
                      ranit B
                      BluShadow wrote:
                      ranit B wrote:
                      But WM_CONCAT is not advisable
                      So why did you mention it? Why recommend someone use a function that will render their application unsupported by Oracle, or that may change it's functionality or even be removed by Oracle in future releases of the database?

                      Better to not mention it at all, rather than mislead people to think they can use it.
                      Just to let him know, that there's a PIT-FALL which you should avoid. Be aware of it and not ignorant...
                      So that, the next time he watches for it and doesn't use or support.

                      Like people say - +'Keep your friends close and enemies closer'+ ;-)
                      • 8. Re: Print Data with Comma Seprated Format.
                        BluShadow
                        ranit B wrote:
                        BluShadow wrote:
                        ranit B wrote:
                        But WM_CONCAT is not advisable
                        So why did you mention it? Why recommend someone use a function that will render their application unsupported by Oracle, or that may change it's functionality or even be removed by Oracle in future releases of the database?

                        Better to not mention it at all, rather than mislead people to think they can use it.
                        Just to let him know, that there's a PIT-FALL which you should avoid. Be aware of it and not ignorant...
                        So that, the next time he watches for it and doesn't use or support.
                        It's mentioned in the FAQ link that it shouldn't be used. There's certainly no need to demonstrate how to use it, when it shouldn't be used.
                        • 9. Re: Print Data with Comma Seprated Format.
                          984891
                          Hi,

                          Try with this.

                          select job,count(job),WM_CONCAT(ename) from emp
                          group by job
                          • 10. Re: Print Data with Comma Seprated Format.
                            BluShadow
                            981888 wrote:
                            Hi,

                            Try with this.

                            select job,count(job),WM_CONCAT(ename) from emp
                            group by job
                            So, over a month later you drag up a thread to demonstrate how to do something in a completely idiotic way, clearly demonstrating you haven't read the above.

                            I sure hope you don't use WM_CONCAT in your own production applications, as you will have rendered the code unsupported by Oracle, and potentially introduced a future bug if the functionality of WM_CONCAT changes in later versions, or if the function is removed altogether.