Search notes:

Oracle SQL MODEL clause example: Incremental string concatenation

The following example demonstrates how strings can be incrementally concatenated by combining analytic functions with the model clause:

create table tq84_model_string_concat (
   id   integer primary key,
   txt  varchar2(10)
);
 
begin
   insert into tq84_model_string_concat values (1, 'one'  );
   insert into tq84_model_string_concat values (2, 'two'  );
   insert into tq84_model_string_concat values (3, 'three');
   insert into tq84_model_string_concat values (6, 'six'  );
   insert into tq84_model_string_concat values (9, 'nine' );
   commit;
end;
/
 
select
   *
from
   tq84_model_string_concat
model
   dimension by (id)
   measures (
      txt,
      cast(null as varchar2(30) ) txt_conc
   )
   rules update automatic order (
     txt_conc[any] = lag(txt_conc) over (order by id) || case when row_number() over (order by id) > 1 then ', ' end || txt[cv()]
   )
;
--
--         ID TXT        TXT_CONC                     
-- ---------- ---------- ------------------------------
--          1 one        one                          
--          2 two        one, two                     
--          3 three      one, two, three              
--          6 six        one, two, three, six         
--          9 nine       one, two, three, six, nine

Index