6 Replies Latest reply on Jan 4, 2010 10:06 AM by rioman

    SELECT SQL for merging text from different rows in one row

    rioman
      Hi,

      I need to create a Select statement to merge some text from several rows in just one. I have a table with the following data:
      SQL> DESC doc_comments
       Name                                                  Null?    Type
       ----------------------------------------------------- -------- ------------------------------------
       DOC                                                            NUMBER
       COMMENTS                                                       VARCHAR2(30)
      
      SQL> SELECT * FROM doc_comments
        2  /
      
             DOC COMMENTS
      ---------- ----------
               1 Note A
               1 Note B
               2 Note C
               2 Note D
               2 Note E
               3 Note F
      
      6 rows selected.
      I need to create a query on that table to show a result like this:
      DOC          COMMENTS
      ------------ --------------------------
                 1 Note A, Note B
                 2 Note C, Note D, Note E
                 3 Note F
      A possible solution would be to create a function to merge the text from different rows and then use it in the query, but I'm not allowed to create any function in the database, I can only use a plain SELECT statement.

      Any ideas on how I can do that?
        • 1. Re: SELECT SQL for merging text from different rows in one row
          Solomon Yakobson
          Assuming comments do not contain pipe (|) character:
          with doc_comments as (
                                select 1 doc,'Note A' comments from dual union all
                                select 1,'Note B' from dual union all
                                select 2,'Note C' from dual union all
                                select 2,'Note D' from dual union all
                                select 2,'Note E' from dual union all
                                select 3,'Note F' from dual
                               )
          select  doc,
                  replace(ltrim(sys_connect_by_path(comments,'|'),'|'),'|',', ') comments
            from  (
                   select  doc,
                           comments,
                           row_number() over(partition by doc order by comments) rn
                     from  doc_comments
                  )
            where connect_by_isleaf = 1
            start with rn = 1
            connect by doc = prior doc
                   and rn  = prior rn + 1
            order by doc
          /
          
                 DOC COMMENTS
          ---------- ----------------------------------------
                   1 Note A, Note B
                   2 Note C, Note D, Note E
                   3 Note F
          
          SQL> 
          And if comments can contain pipe (|) then replace pipe in the above code with a character not used in comments.

          SY.
          • 2. Re: SELECT SQL for merging text from different rows in one row
            Frank Kulash
            Hi,

            What you want is called String Aggregation .

            The best way to do it depends on your exact needs, and your version of Oracle. It's always a good idea to say what version you're using.
            Starting in Oracle 11.2, there's a built-in function, LISTAGG, that does that.
            Starting in Oracle 10, you may have a built-in but undocumented function WMSYS.WMCONCAT, that is similar.

            In Oracle 9 and up, you can do this:
            WITH     got_rnum     AS
            (
                 SELECT     doc
                 ,     comments
                 ,     ROW_NUMBER () OVER ( PARTITION BY  doc
                                     ORDER BY        comments
                                   )     AS rnum
                 FROM     doc_comments
            )
            SELECT     doc
            ,       LTRIM ( SYS_CONNECT_BY_PATH (comments, ', ')
                       , ', '
                       )          AS all_comments
            FROM     got_rnum
            WHERE     CONNECT_BY_ISLEAF     = 1
            START WITH     rnum     = 1
            CONNECT BY     rnum     = PRIOR rnum + 1
                 AND     doc     = PRIOR doc
            ;
            For explanation and other techniques for doing String Aggregation, see OracleBase or AskTom].
            Too bad you can't create a function. STRAGG (which you can copy from AskTom, and which does the same thing as WM_CONCAT) is very easy to use.

            Edited by: Frank Kulash on Jan 3, 2010 5:22 PM
            1 person found this helpful
            • 3. Re: SELECT SQL for merging text from different rows in one row
              Solomon Yakobson
              Hi Frank,

              I assume in real table document comments will (sooner or later) have ', ' as part of a comment and sys_connect_by_path will fail with:
              ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
              SY.
              • 4. Re: SELECT SQL for merging text from different rows in one row
                730428
                This proble is known as "string aggregation", that's my favourite way to solve it:
                SQL> select doc, ltrim(sys_connect_by_path(comments,','),',') comments
                  2  from (select doc, comments, row_number() over (partition by doc order by comments) rn
                  3          from doc_comments
                  4       )
                  5  where connect_by_isleaf=1
                  6  connect by prior rn+1=rn and prior doc=doc
                  7  start with rn=1;
                
                       DOC COMMENTS
                ---------- ------------------------------
                         1 Note A,Note B
                         2 Note C,Note D,Note E
                         3 Note F
                Max
                [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/02/query-gerarchiche/]
                1 person found this helpful
                • 5. Re: SELECT SQL for merging text from different rows in one row
                  Solomon Yakobson
                  Also, keep in mind - sys_connect_by_path returns VARCHAR2, so combined document comments can not exceed 4000 bytes. If, in your case, combined document comments can exceed 4000 bytes you could create user defined aggregate function CLOBAGG:
                  create or replace
                    type clobagg_type as object(
                                                text clob,
                                                static function ODCIAggregateInitialize(
                                                                                        sctx in out clobagg_type
                                                                                       )
                                                  return number,
                                                member function ODCIAggregateIterate(
                                                                                     self  in out clobagg_type,
                                                                                     value in     clob
                                                                                    )
                                                  return number,
                                                member function ODCIAggregateTerminate(
                                                                                       self        in  clobagg_type,
                                                                                       returnvalue out clob,
                                                                                       flags in number
                                                                                      )
                                                  return number,
                                                member function ODCIAggregateMerge(
                                                                                   self in out clobagg_type,
                                                                                   ctx2 in     clobagg_type
                                                                                  )
                                                  return number
                                               );
                  / 
                  create or replace
                    type body clobagg_type
                      is
                        static function ODCIAggregateInitialize(
                                                                sctx in out clobagg_type
                                                               )
                          return number
                          is
                          begin
                              sctx := clobagg_type(null) ;
                              return ODCIConst.Success ;
                        end;
                        member function ODCIAggregateIterate(
                                                             self  in out clobagg_type,
                                                             value in     clob
                                                            )
                          return number
                          is
                          begin
                              self.text := self.text || value ;
                              return ODCIConst.Success;
                        end;
                        member function ODCIAggregateTerminate(
                                                               self        in  clobagg_type,
                                                               returnvalue out clob,
                                                               flags       in  number
                                                              )
                          return number
                          is
                          begin
                              returnValue := self.text;
                              return ODCIConst.Success;
                          end;
                        member function ODCIAggregateMerge(
                                                           self in out clobagg_type,
                                                           ctx2 in     clobagg_type
                                                          )
                          return number
                          is
                          begin
                              self.text := self.text || ctx2.text;
                              return ODCIConst.Success;
                          end;
                  end;
                  /
                  create or replace
                    function clobagg(
                                     input clob
                                    )
                      return clob
                      deterministic
                      parallel_enable
                      aggregate using clobagg_type;
                  / 
                  Now you can:
                  with doc_comments as (
                                        select 1 doc,'Note A' comments from dual union all
                                        select 1,'Note B' from dual union all
                                        select 2,'Note C' from dual union all
                                        select 2,'Note D' from dual union all
                                        select 2,'Note E' from dual union all
                                        select 3,'Note F' from dual
                                       )
                  select  doc,
                          rtrim(clobagg(comments || ', '),', ') comments
                    from  doc_comments
                    group by doc
                    order by doc
                  /
                  
                         DOC COMMENTS
                  ---------- ----------------------------------------
                           1 Note A, Note B
                           2 Note C, Note E, Note D
                           3 Note F
                  
                  SQL> 
                  SY.
                  • 6. Re: SELECT SQL for merging text from different rows in one row
                    rioman
                    I'm working on 11gr1 and therefore I can't use the native analytic function listagg. But I've tried the query with sys_connect_by_path and it works perfectly for me. Thanks a lot for your help.