In the aggregate results from the Tom Kyte's STRAGG tutorial the delimited values in the string produced for Group 4, i.e. 'a,b,b,a', were in no particular order. While the analytic version of STRAGG allowed us to control the sort order easily, no aggregate functions, including STRAGG, provide a built-in concept of sort order.
To control the sort order when STRAGG is used in its aggregate form an
enhanced version of STRAGG_TYPE is required
(changes from the prior version are indicated with
-- deleted
and
-- added.
create or replace type stragg_type as object
(
-- string varchar2(4000), -- deleted
val_table varchar2_table_type , -- added
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 ) ; -- deleted
sctx := stragg_type( varchar2_table_type() ); -- added
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin
-- self.string := self.string || ',' || value; -- deleted
self.val_table.extend; -- added
self.val_table(self.val_table.count) := value; -- added
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
v_data varchar2(4000); -- added
begin
-- returnValue := ltrim(self.string, ','); -- deleted
for x in -- added
( -- added
select column_value -- added
from table(val_table) -- added
order by 1 -- added, define sort order here
) -- added
loop -- added
v_data := v_data || ',' || x.column_value;-- added
end loop; -- added
returnValue := ltrim( v_data, ',' ); -- added
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; -- deleted
for i in 1 .. ctx2.val_table.count -- added
loop -- added
self.val_table.extend; -- added
self.val_table(self.val_table.count) := ctx2.val_table(i); -- added
end loop; -- added
-- in 10g this for loop could probably be replaced with the following
-- self.val_table := self.val_table multiset union ( ctx2.val_table ) ;
return ODCIConst.Success;
end;
end;
/
create or replace function stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/
With this new version of the solution the delimited values for Group 4 will appear in ascending order, 'a,a,b,b'.
set null "(null)" select group_key , STRAGG( VAL ) as string from t group by group_key ;
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 Group 6 (null) Group 7 (null) Group 8 a,a,b,b (null) (null)