Oracle String Aggregrator Function

Straggle Function:
This shows how to collect values from multiple rows into a single, comma delimited string. This is also known as "string aggregation".

Suppose input is something like this..

Input table

TBLEQUIPMENTIDENTIFIER
nodeid      attributeid      value
2348       username      user01
2348       password      mypass
2349       username      user02
2349       password      yourpass
2348       vlanid      net771

How it helps:
You can use it in a SELECT statement like so:

SELECT nodeid, Stragg( attributename ||'='|| VALUE ) IDENTIFIER  
                      FROM   TBLEQUIPMENTIDENTIFIER 
                      GROUP BY nodeid;

Open in new window

... to obtain output like so:

Nodeid      Identifiers

2348      username=user01,password=mypass
2349      vlanid=net771,username=user02,password=yourpass

Thus, you get a concatenated value in your output.

This technique originally published by Tom Kyte at Ask Tom "Function to concatenate output".
More information on this can be obtained at sqlsnippet.com

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
                      ;
                      /

Open in new window

Oracle String Aggregrator Function

Comments (1)