1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to get group by records as comma separated values.,?!

Discussion in 'SQL PL/SQL' started by Vicky, Jul 11, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The query below returns,

    select count(*),city from employees group by city


    COUNT(emp_name) CITY
    ------------------ -----
    3 Delhi
    4 Mumbai
    4 Chennai



    In the place of count(*), I want all the column values to be displayed as comma separated values for the respective city.,
    like

    Records CITY
    -------- ----------
    arun,vijay,sam Delhi
    Raja,santhosh,Mani Mumbai
    Keerthi,Karthi,Seema Chennai
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Use string aggregation

    For example :
    Code (SQL):

    WITH your_data AS
    (
    SELECT 'arun' emp_name,'Delhi' city FROM dual UNION ALL
    SELECT 'vijay' , 'Delhi' FROM dual UNION ALL
    SELECT 'sam' ,'Delhi' FROM dual UNION ALL
    SELECT 'Raja','Mumbai' FROM dual UNION ALL
    SELECT 'santhosh','Mumbai' FROM dual
    )

    SELECT
           city,
           rtrim (xmlagg (xmlelement (emp_name, emp_name || ',')).EXTRACT ('//text()'), ',')        

    FROM your_data
    GROUP BY city ;

     
     
    Vicky likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Try this....
    select
    rtrim (xmlagg (xmlelement (e, emp_name || ',')).extract ('//text()'), ',') enames,city
    from
    employees
    group by
    city
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Frndz..,

    How about giving more than column.,

    For ex.,

    SELECT 'arun' emp_name, 1 emp_id, '5k' salary, 'Delhi' city FROM dual
    union all
    SELECT 'bala' emp_name, 2 emp_id, '5.6k' salary, 'Chennai' city FROM dual

    EMP_NAME EMP_ID SALARY CITY
    -------- ------ ------ -------
    arun 1 5k Delhi
    bala 2 5.6k Chennai

    if I give arun, means all his details should come as
    arun, 1, 5k, Delhi ..

    How could v do dis.,?!
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    You 1 line gives the record for 1 employee:

    Try this:

    Code (SQL):
    WITH emp_data AS
    (
    SELECT 'arun' emp_name, 1 emp_id, '5k' salary, 'Delhi' city FROM dual
    UNION ALL
    SELECT 'bala' emp_name, 2 emp_id, '5.6k' salary, 'Chennai' city FROM dual
    )
    SELECT emp_name||','||emp_id||','||salary||','||city
    FROM emp_data
    WHERE emp_name = '&emp_name';
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Use listagg in 11g and later releases:


    Code (SQL):

    SQL> SELECT job_id, listagg(last_name, ',') WITHIN GROUP (ORDER BY job_id) namelist
      2  FROM employees
      3  GROUP BY job_id;


    JOB_ID     NAMELIST
    ---------- ------------------------------------------------------------------------------------------------------------------------
    AC_ACCOUNT Gietz
    AC_MGR     Higgins
    AD_ASST    Whalen
    AD_PRES    King
    AD_VP      De Haan,Kochhar
    FI_ACCOUNT Chen,Faviet,Popp,Sciarra,Urman
    FI_MGR     Greenberg
    HR_REP     Mavris
    IT_PROG    Austin,Ernst,Hunold,Lorentz,Pataballa
    MK_MAN     Hartstein
    MK_REP     Fay
    PR_REP     Baer
    PU_CLERK   Baida,Colmenares,Himuro,Khoo,Tobias
    PU_MAN     Raphaely
    SA_MAN     Cambrault,Errazuriz,Partners,Russell,Zlotkey
    SA_REP     Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Doran,Fox,GRANT,Greene,Hall,Hutton,Johnson,King,Kumar,Lee,Livingston,Mar
               vins,McEwen,Olsen,Ozer,Sewall,Smith,Smith,Sully,Taylor,Tucker,Tuvault,Vishney
    SH_CLERK   Bell,Bull,Cabrio,Chung,Dellinger,Dilly,Everett,Feeney,Fleaur,Gates,Geoni,GRANT,Jones,McCain,OConnell,Perkins,Sarchand,Su
               llivan,Taylor,Walsh
    ST_CLERK   Atkinson,Bissot,Davies,Gee,Ladwig,Landry,Mallin,Markle,Marlow,Matos,Mikkilineni,Nayer,Olson,Patel,Philtanker,Rajs,Rogers
               ,Seo,Stiles,Vargas
    ST_MAN     Fripp,Kaufling,Mourgos,Vollman,Weiss


    19 ROWS selected.


    SQL>