Analytics 2.0

Jumping ahead from basic analytics to analytics 2.0 ( new feature in 11g R2) 

One simple yet  neat , nicer addition to analytics family in 11g R2.
There is a new analytic function called LISTAGG  , where I could concatenate the list of the data values in a
particular group .

Prior to this release , we would have used hierarchical  query ... SYS_PATH to a achieve this result set.


Let us proceed with a simple example


SQL> Create table  t as select * from all_objects;

Table created

SQL>
SQL>         Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'OUTLN')
  6           group by owner , object_type
  7           order by object_type
  8  ;

OWNER                          OBJECT_TYPE         LISTAGG(OBJECT_NAME,'~')WITHIN
------------------------------ ------------------- --------------------------------------------------------------------------------
OUTLN                          INDEX               OL$HNT_NUM~OL$NAME~OL$NODE_OL_NAME~OL$SIGNATURE
SCOTT                          INDEX               PK_DEPT~PK_EMP
OUTLN                          PROCEDURE           ORA$GRANT_SYS_SELECT
OUTLN                          TABLE               OL$~OL$HINTS~OL$NODES
SCOTT                          TABLE               BONUS~DEPT~EMP~SALGRADE


Note : ~ is the delimiter

Bear in mind , the result set is of datatype varchar2( unless otherwise the columns defined in the group are RAW) , so you may be hit the limitation of varchar2(4000).

SQL>        Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'SYS')
  6           group by owner , object_type
  7           order by object_type
  8  ;

Select owner , object_type , listagg(object_name , '~')
         within group
         (order by object_name )
         from t
         where owner in ('SCOTT' , 'SYS')
         group by owner , object_type
         order by object_type

ORA-01489: result of string concatenation is too long

Have a good time with analytcs.
Please refer to http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm for additional informaiton .