<

Oracle String Aggregrator Function

Published on
13,076 Points
6,976 Views
1 Endorsement
Last Modified:
Approved
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

1
Author:scorneo
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free