<

Oracle String Aggregrator Function

Published on
12,838 Points
6,738 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

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month