This discussion is archived
10 Replies Latest reply: Jan 15, 2013 12:43 AM by BluShadow RSS

Print Data with Comma Seprated Format.

Sudhir_Meru Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    http://www.dba-oracle.com/t_converting_rows_columns.htm
  • 3. Re: Print Data with Comma Seprated Format.
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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.

Legend

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