Converting rows to strings

1 Preface

Consider the following table:

But what you really want is this:

Or even better sorted alphabetically

This document describes how you can achieve this in OBIEE against an Oracle DB.

2 Stragg function

This solution is based on Tom Kite’s original String Aggregation (STRAGG) function found here:

http://www.sqlsnippets.com/en/topic-11591.html

To implement this function in OBIEE your first have to bring the function into the database. Either in the data-schema or in your custom OBIEE function schema. If you do the later be sure that the OBIEE function schema has direct select rights granted on the data-schema tables and views. Grant an execute on the STRAGG function to public. It’s also very handy to create a public SYNONYM for the STRAGG function.

2.1 The STRAGG scripts

2.1.1 The STRAGG object

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

);

/

2.1.2 The STRAGG type body

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;

/

2.1.3 The STRAGG function

create or replace function stragg

( input varchar2 )

return varchar2

deterministic

parallel_enable

aggregate using stragg_type

;

/

3 OBIEE Usage

These function work both from the repository as directly from the reports.

3.1 Unsorted

EVALUATE_AGGR( ‘STRAGG(%1)’ as varchar(200), EMP.ENAME)

3.2 Sorted

EVALUATE( ‘STRAGG(%1) OVER ( PARTITION BY (%2) ORDER BY (%1) ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ‘ as varchar(200), EMP.ENAME,DEPT.DEPTNO)

3.3 Distinct

EVALUATE_AGGR( ‘STRAGG( DISTINCT %1)’ as varchar(200), EMP.ENAME)

Till Next Time

John Minkjan is a senior BI-consultant at Ciber in the Netherlands, the text of this article is also published on his personal blog http://obiee101.blogspot.com/