<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Oracle String Aggregrator Function

Published on
12,890 Points
6,790 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
Comment
Author:scorneo
1 Comment
LVL 3

Author Comment

by:scorneo
Thank you sir
0

Featured Post

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Join & Write a Comment

Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month