1 2 Previous Next 21 Replies Latest reply: Oct 19, 2011 11:11 AM by 895288 RSS

    concatenate group by row values

    user6287828
      Hello

      this might be a very common question. How to concatenate string belonging to group by clause.

      A similar question was asked somewhere here
      Query to convert Row to column - Re-posting

      but the suggested method ( given on that page as well as this link
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
      ) only works when the group by is an number type.
      Like the exmple used on above page has group by deptno.

      But in my case its like ...both columns are varchar

      locationid employeeName
      AA Robert
      AA Jennifer
      AB Walsh
      AC Sophie
      AB David
      AB Neha

      and I need something like this


      locationID empleeList
      AA Robert, Jennifer
      AB Walsh,David, Neha
      AC Sophie


      when I execute ..it just never returns the control and keeps executing
        • 1. Re: concatenate group by row values
          LKBrwn_DBA
          Try this:
          SQL> Select department_id, Wm_Concat(last_name) Personnel
            2    From hr.employees
            3*  Group By department_id;
          
          DEPARTMENT_ID PERSONNEL
          ------------- ------------------------------------------------------------
                     10 Whalen
                     20 Hartstein,Fay
                     30 Raphaely,Khoo,Tobias,Baida,Colmenares,Himuro
                     40 Mavris
                     50 Weiss,Landry,Philtanker,Gee,Rogers,Mallin,Olson,Marlow,Atkin
                        son,Bissot,Markle,Taylor,Vargas,Matos,Davies,Rajs,Patel,Seo,
                        Stiles,Ladwig,Grant,OConnell,Feeney,Walsh,Jones,McCain,Evere
                        tt,Bell,Perkins,Gates,Dilly,Chung,Cabrio,Dellinger,Bull,Sarc
                        hand,Geoni,Sullivan,Fleaur,Mikkilineni,Fripp,Vollman,Mourgos
                        ,Nayer,Kaufling
          
                     60 Hunold,Ernst,Austin,Lorentz,Pataballa
                     70 Baer
                     80 Russell,Zlotkey,Bernstein,Taylor,Hutton,Abel,Kumar,Bates,Smi
                        th,Fox,Bloom,Ozer,Johnson,Livingston,Banda,Ande,Lee,Marvins,
                        Greene,Vishney,Sewall,Doran,Smith,McEwen,Sully,King,Tuvault,
                        Cambrault,Olsen,Hall,Tucker,Cambrault,Partners,Errazuriz
          
                     90 King,Kochhar,De Haan
                    100 Greenberg,Chen,Urman,Popp,Sciarra,Faviet
                    110 Higgins,Gietz
          :p
          • 2. Re: concatenate group by row values
            user6287828
            Wm_Concat invalid identifier
            • 3. Re: concatenate group by row values
              SanjayRs
              Wm_Concat is undocumented

              Use stragg function from AskTom site
              select d.dname, stragg(e.ename) emp_list
              from dept d , emp e
              where d.deptno = e.deptno
              group by d.dname
              DNAME          EMP_LIST
              -------------- ---------------------------------------
              ACCOUNTING     CLARK,KING,MILLER
              RESEARCH       JONES,SCOTT,SMITH,FORD,ADAMS
              SALES          WARD,ALLEN,BLAKE,MARTIN,JAMES,TURNER
              Or
              stragg code
              create or replace type stragg_type as object
              (
                string varchar2(4000),
              
                static function ODCIAggregateInitialize
                  ( sctx in out stragg_type )
                  return number ,
              
                member function ODCIAggregateIterate
                  ( self  in out stragg_type ,
                    value in     varchar2
                  ) return number ,
              
                member function ODCIAggregateTerminate
                  ( self        in  stragg_type,
                    returnvalue out varchar2,
                    flags in number
                  ) return number ,
              
                member function ODCIAggregateMerge
                  ( self in out stragg_type,
                    ctx2 in     stragg_type
                  ) return number
              );
              /
              
              create or replace type body stragg_type
              is
              
                static function ODCIAggregateInitialize
                ( sctx in out stragg_type )
                return number
                is
                begin
              
                  sctx := stragg_type( null ) ;
              
                  return ODCIConst.Success ;
              
                end;
              
                member function ODCIAggregateIterate
                ( self  in out stragg_type ,
                  value in     varchar2
                ) return number
                is
                begin
              
                  self.string := self.string || ',' || value ;
              
                  return ODCIConst.Success;
              
                end;
              
                member function ODCIAggregateTerminate
                ( self        in  stragg_type ,
                  returnvalue out varchar2 ,
                  flags       in  number
                ) return number
                is
                begin
              
                  returnValue := ltrim( self.string, ',' );
              
                  return ODCIConst.Success;
              
                end;
              
                member function ODCIAggregateMerge
                ( self in out stragg_type ,
                  ctx2 in     stragg_type
                ) return number
                is
                begin
              
                  self.string := self.string || ctx2.string;
              
                  return ODCIConst.Success;
              
                end;
              
              end;
              /
              
              create or replace function stragg
                ( input varchar2 )
                return varchar2
                deterministic
                parallel_enable
                aggregate using stragg_type
              ;
              /
              • 4. Re: concatenate group by row values
                LKBrwn_DBA
                WM_CONCAT is part of Work Manager (WMSYS schema) which you could install.

                Otherwise, forget those other c.r.a.p.y. functions here is another standard Oracle function:
                col personnel format a50
                select department_id,substr(max(sys_connect_by_path(last_name,',')),2) Personnel
                  from (select department_id,last_name,row_number() over (partition by department_id order by last_name) rn
                          from hr.employees)
                  start with rn=1
                connect by prior rn = rn - 1
                    and prior department_id = department_id
                  group by department_id
                /
                DEPARTMENT_ID PERSONNEL
                ------------- --------------------------------------------------
                           10 Whalen
                           20 Fay,Hartstein
                           30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
                           40 Mavris
                           50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dell
                              inger,Dilly,Everett,Feeney,Fleaur,Fripp,Gates,Gee,
                              Geoni,Grant,Jones,Kaufling,Ladwig,Landry,Mallin,Ma
                              rkle,Marlow,Matos,McCain,Mikkilineni,Mourgos,Nayer
                              ,OConnell,Olson,Patel,Perkins,Philtanker,Rajs,Roge
                              rs,Sarchand,Seo,Stiles,Sullivan,Taylor,Vargas,Voll
                              man,Walsh,Weiss
                
                           60 Austin,Ernst,Hunold,Lorentz,Pataballa
                           70 Baer
                           80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Ca
                              mbrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Joh
                              nson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olse
                              n,Ozer,Partners,Russell,Sewall,Smith,Smith,Sully,T
                              aylor,Tucker,Tuvault,Vishney,Zlotkey
                
                           90 De Haan,King,Kochhar
                          100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
                          110 Gietz,Higgins
                :p
                • 5. Re: concatenate group by row values
                  Christian Balz
                  maybe this,
                  SQL> With Tab As(Select 'AA' locationid, 'Robert'   employeeName From Dual Union All
                    2              Select 'AA' locationid, 'Jennifer' employeeName From Dual Union All
                    3              Select 'AB' locationid, 'Walsh'    employeeName From Dual Union All
                    4              Select 'AC' locationid, 'Sophie'   employeeName From Dual Union All
                    5              Select 'AB' locationid, 'David'    employeeName From Dual Union All
                    6              Select 'AB' locationid, 'Neha'     employeeName From Dual)
                    7  SELECT locationid
                    8        ,RTrim(xmlagg(xmlelement(a,employeeName || ',').extract('//text()')),',') employeeName
                    9    FROM Tab
                   10   Group BY locationid;
                  
                  LOCATIONID EMPLOYEENAME
                  ---------- --------------------------------------------------------------------------------
                  AA         Robert,Jennifer
                  AB         Walsh,David,Neha
                  AC         Sophie
                  Regards,
                  Christian Balz
                  • 6. Re: concatenate group by row values
                    user6287828
                    numeric or value error: character string buffer too small
                    ORA-06512: at "STRING_AGG_TYPE", line 17
                    • 7. Re: concatenate group by row values
                      user6287828
                      Is this due to xmlagg that I again got
                      ORA-19011: Character string buffer too small

                      while trying this.
                      • 8. Re: concatenate group by row values
                        666352
                        You can post your version?

                        With 10g
                        select  locationid ,substr(listemployee,2) listemployee
                        from your_table -- put here the name of your table 
                        model
                        return updated rows
                        partition by (locationid )
                        dimension by (row_number()over(partition by  locationid order by employeeName) rn)
                        measures(cast(employeeName   as varchar2(4000))  listemployee) ignore nav
                        rules
                        iterate(1000)
                        until( presentv(listemployee[iteration_number+2],1,0)=0)
                        (listemployee[0]=listemployee[0]||' - '||listemployee[iteration_number+1])
                        /
                        SQL> column  listemployee  format  a50
                        SQL> with t as (  
                          2  select 'AA' locationid ,'Robert' employeeName from dual union all
                          3  select 'AA','Jennifer'  from dual union all
                          4  select 'AB','Walsh'  from dual union all
                          5  select 'AC','Sophie'  from dual union all
                          6  select 'AB','David'  from dual union all
                          7  select 'AB','Neha'  from dual )
                          8  select  locationid ,substr(listemployee,2) listemployee
                          9  from t
                         10  model 
                         11  return updated rows 
                         12  partition by (locationid )
                         13  dimension by (row_number()over(partition by  locationid order by employeeName) rn)
                         14  measures(cast(employeeName   as varchar2(4000))  listemployee) ignore nav
                         15  rules 
                         16  iterate(1000)
                         17  until( presentv(listemployee[iteration_number+2],1,0)=0)
                         18  (listemployee[0]=listemployee[0]||' - '||listemployee[iteration_number+1])
                         19  /
                        
                        LO LISTEMPLOYEE
                        -- --------------------------------------------------
                        AA - Jennifer - Robert
                        AC - Sophie
                        AB - David - Neha - Walsh
                        
                        SQL> 
                        • 9. Re: concatenate group by row values
                          SanjayRs
                          You can use the SYS_CONNECT_BY_PATH function available, Probably this would works for you.
                          select
                             DEPTNO,
                             replace(substr(max(sys_connect_by_path(ENAME,'/')),2),'/',',') Names_list
                          from
                          (
                             select
                               DEPTNO,
                               ENAME,
                               row_number() over (partition by DEPTNO order by DEPTNO) rn
                               from (
                                  select * from emp ) tab_
                          )
                          start with rn =1
                          connect by prior DEPTNO  = DEPTNO
                          and
                          prior rn+1 = rn
                          group by DEPTNO
                          order by DEPTNO
                          /
                              DEPTNO NAMES_LIST
                          ---------- --------------------------------------
                                  10 CLARK,KING,MILLER
                                  20 SMITH,FORD,ADAMS,SMITH,SCOTT
                                  30 ALLEN,TURNER,ALLEN,JAMES,BLAKE,MARTIN
                          • 10. Re: concatenate group by row values
                            SanjayRs
                            user6287828 wrote:
                            numeric or value error: character string buffer too small
                            ORA-06512: at "STRING_AGG_TYPE", line 17
                            Increasing the the size of the string to as shown below DOES NOT WORK!

                            Compiles with 32767

                            create or replace type stragg_type as object
                            (
                            {color:blue} string varchar2(32767),{color}

                            static function ODCIAggregateInitialize
                            ( sctx in out stragg_type )
                            return number ,

                            SS
                            • 11. Re: concatenate group by row values
                              ShahidAli
                              Is it ok i provide the solution in PL/SQL.
                              • 12. Re: concatenate group by row values
                                LKBrwn_DBA
                                Ok, ok...
                                WITH t AS (
                                        SELECT 'AA' locationid, 'Robert' employeename FROM DUAL UNION ALL
                                        SELECT 'AA', 'Jennifer' FROM DUAL        UNION ALL
                                        SELECT 'AB', 'Walsh'    FROM DUAL        UNION ALL
                                        SELECT 'AC', 'Sophie'   FROM DUAL        UNION ALL
                                        SELECT 'AB', 'David'    FROM DUAL        UNION ALL
                                        SELECT 'AB', 'Neha'     FROM DUAL)
                                SELECT     locationid,
                                           SUBSTR (MAX (SYS_CONNECT_BY_PATH (employeename, ',')), 2) personnel
                                      FROM (SELECT locationid, employeename,
                                                   ROW_NUMBER () OVER (PARTITION BY locationid ORDER BY employeename) rn
                                              FROM t)
                                START WITH rn = 1
                                CONNECT BY PRIOR rn = rn - 1 AND PRIOR locationid = locationid
                                  GROUP BY locationid
                                /
                                LO PERSONNEL
                                -- --------------------------------------------------
                                AA Jennifer,Robert
                                AC Sophie
                                AB David,Neha,Walsh
                                :p
                                • 13. Re: concatenate group by row values
                                  SanjayRs
                                  Also using SYS_CONNECT_BY_PATH in the Query got error as below.
                                                        *
                                  ERROR at line 11:
                                  ORA-01489: result of string concatenation is too long
                                  • 14. Re: concatenate group by row values
                                    user6287828
                                    Thank you everyone.
                                    Though nothing is working ...for me.

                                    It takes like 7 minutes and keeep saying executing and than it says "Character string buffer too small"


                                    So may be a bit more detail might help.
                                    My table for which this has to be done is not at all big, It has just few rows,(5-6)

                                    My code is perfectly same as many of you have suggested. I am using TOAD and rest of BIG things just compile and execute fine.
                                    1 2 Previous Next