2010-11-26

LISTAGG: 11g r2 new feature

LISTAGG it's a new aggregate function in Oracle 11g release 2. It performs string aggregation.
LISTAGG can optionally be used as an analytic (i.e. the optional OVER() clause).


LISTAGG Syntax structure:


LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]




Example:

SELECT
LISTAGG(sigla, '; ')
WITHIN GROUP (ORDER BY sigla) resultado
FROM instituicoes;


Some previous alternatives:
using CAST:

select varchar2_util.to_string(CAST( COLLECT( sigla ) as t_varchar2_sql_line))
resultado
FROM instituicoes


using CONNECT BY:

SELECT *
FROM ( SELECT LTRIM (SYS_CONNECT_BY_PATH (sigla, ','), ',') resultado
FROM ( SELECT sigla, ROWNUM rn
FROM instituicoes
ORDER BY sigla)
CONNECT BY PRIOR rn = rn - 1
START WITH rn = 1
ORDER BY LEVEL DESC)
WHERE ROWNUM = 1


In my tests using the above like expressions using the CAST with the function was 2,2x than the time of new LISTAGG and CONNECT BY took 1,5x.

No comments:

Post a Comment

Os comentários são moderados.
The comments are moderated.