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.