SQL & PL/SQL

Hi All,

Questions about string aggregation (i.e. combining values from multiple rows into a single string, like "a,b,c") appear often on this forum so I thought you may be interested in some tutorials I've written that explain and compare some popular methods, like those that use STRAGG, CONNECT BY, COLLECT, and XMLAGG.

The tutorials start with a summary of all these techniques at this page.

http://www.sqlsnippets.com/en/topic-11787.html

You may also be interested in some performance comparison charts at this page.

http://www.sqlsnippets.com/en/topic-11783.html

Beyond the established techniques, I also talk about a new technique I developed that loops through a reference model to produce the end result. The solution looks like this.

create table t6( group_key varchar2(10), val varchar2(10) );

insert into t6 values ( 'Group 1' , 'a'  );
insert into t6 values ( 'Group 2' , 'a'  );
insert into t6 values ( 'Group 2' , 'b'  );
insert into t6 values ( 'Group 3' , 'a'  );
insert into t6 values ( 'Group 3' , 'b'  );
insert into t6 values ( 'Group 3' , 'c'  );
insert into t6 values ( 'Group 4' , 'a'  );
insert into t6 values ( 'Group 4' , 'a'  );
insert into t6 values ( 'Group 4' , 'b'  );
insert into t6 values ( 'Group 4' , 'b'  );
insert into t6 values ( 'Group 5' , 'a'  );
insert into t6 values ( 'Group 5' , 'b'  );
insert into t6 values ( 'Group 5' , null );
insert into t6 values ( 'Group 5' , 'd'  );
insert into t6 values ( 'Group 5' , 'e'  );
insert into t6 values ( 'Group 6' , null );

commit;

column group_key format a10
column string    format a15

select
  group_key ,
  substr( string, 2 ) as string
from
  dual
where
  1 = 2
model
  reference t6_ref
    on
    (
      select
        row_number() over ( order by val ) - 1 as row_num ,
        count(*) over () - 1                   as max_row_num ,
        group_key ,
        val
      from
        t6
      where
        val is not null
      order by
        val
    )
    dimension by( row_num )
    measures    ( max_row_num, group_key, val )
  main t6_main
    dimension by ( cast( null as varchar2(4000) ) as group_key )
    measures     ( cast( null as varchar2(4000) ) as string )
    rules
      iterate( 4294967295 )
      until
      ( t6_ref.max_row_num[0] is null or
        iteration_number >= t6_ref.max_row_num[0]
      )
    (
      string[ t6_ref.group_key[iteration_number] ] =
        string[ cv() ] || ',' ||
        t6_ref.val[ iteration_number ]
    )
order by
  1
;

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e

The new reference model technique is explained in detail at this page.

http://www.sqlsnippets.com/en/topic-11754.html

I'd be interested in hearing if you find these resources useful or if there are any bugs or mistakes in my work.

Thanks.