Arquivo da tag: query

Como agrupar várias linhas em apenas uma no Oracle ?

Olá gente, aqui estou eu mais uma vez para lhes dar outra dica simples, porém muito útil no dia-a-dia.

Por vezes me deparei com situações em que uma query retornava vários registros, mas eu precisava de apenas 1 registro, com os resultados separados por vírgula, por exemplo. Vou apresentar neste post as 3 maneiras mais simples que encontrei de realizar isto.

Para exemplificar, segue a query “CRUA”:

select ename from emp

ENAME
----------
KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
FORD
SMITH
SCOTT
ADAMS
MILLER

13 linhas selecionadas

Bom, a primeira (e mais simples) maneira de agrupar essas linhas em uma única linha, é usando a função de grupo (não documentada) WM_CONCAT, que está presente a partir do Oracle 10g. Essa função agrupa a coluna e separa os resultados por uma vírgula (não tem como mudar). Ficaria então assim:

select wm_concat(ename) from emp

ENAME
----------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER

1 linha selecionada

Um dos problemas desta função é que não está presente em todos os bancos Oracle, e nem tem tem esse dever por não ser documentada. Outro ponto negativo é que os registros sempre são divididos por vírgulas.

A próxima saída para nosso problema seria fazer um autêntico “migué”, utilizando xml para resolver o problema.  MAS COMO ?!
A ideia é simples, criamos um xml com a função xmlelement, depois extraímos o texto do xml e aplicamos a função de grupo xmlagg que é a grande responsável por agrupar os resultados.

Segue código:

select rtrim(xmlagg(xmlelement(e,ename,';').extract('//text()')),';') from emp;

ENAME
----------
ADAMS;ALLEN;BLAKE;CLARK;FORD;JAMES;JONES;KING;MARTIN;MILLER;SCOTT;SMITH;TURNER

1 linha selecionada

Sucesso!
O ponto positivo desta técnica é que podemos agora escolher qual será o separador das strings (no caso eu utilizei um ponto e vírgula). Acredito que essa função xml também só está presente a partir do 10g.

A última solução (mas não menos importante) que irei abordar no tópico de hoje se refere ao comando LISTAGG que está presente nos bancos 11g a partir do release 2.
O grande ponto forte deste comando (além de sua simplicidade, e a capacidade de escolher o separador) é a possibilidade de ordenação dos registros da lista.

Segue código:

select LISTAGG(ename, '#') WITHIN GROUP (ORDER BY ename desc)  from emp;

ENAME
----------
TURNER#SMITH#SCOTT#MILLER#MARTIN#KING#JONES#JAMES#FORD#CLARK#BLAKE#ALLEN#ADAMS

1 linha selecionada

Bom é isso aí gente!
Se rolar alguma dúvida é só postar!

Até mais!

Não há como juntar o pó, se continuo insistindo em sentir a ventania.
–  Reynaldo Martins