Agrupar!

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

24 opiniões sobre “Como agrupar várias linhas em apenas uma no Oracle ?”

  1. Nunca tinha usado wm_concat, Eficaz
    Mas acho que o xml ainda seja mais eficiente dentre as opções!

    Gostei do LISTAGG , mas só no 11g ? “/

    ps: to vendo que esse blog vai se tornar um favorito quando eu for dar um google! 😉

    #sucesso

    1. Concordo… no banco 10g o melhor eh por xml(pq o wm_concat é limitado e não-documentado), mas no 11g acho melhor usar o comando que foi feito pra isso ne (:

  2. Pessoal,
    Fica a dica, consegui resolver o problema do separador do wm_concat da seguinte forma:

    select replace(wm_concat(ename),’,’,’ / ‘) from emp

    Funcionou perfeitamente.

    Boa sorte!

    1. Olá Giovani,

      Acho que uma solução seria dar o distinct nos dados em uma query e a utilizar como subquery para outra com o agrupamento.
      Ex:

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

      Espero ter ajudado !

  3. Estou utilizando a função do XML, mas o meu retorno está sendo maior que 4000 carácteres o que causa o erro ORA-19011: Character string buffer too small. Tem co me ajudar nesse sentido?

    1. Claudio,

      Será que o seu problema não está no rtrim ?
      Porque o xml do oracle trabalha como clob.
      Qualquer coisa, vale também a pena dar uma conferida no comando getClobVal()
      Mas se estiver usando 11g, te aconselho a usar o listagg.

      Abs

  4. Olá,
    Sabe alguma função para fazer ao contrário, tenho uma registro assim:
    select info from emp;
    info
    ==========================
    1245;45678;7789;64646;4478

    queria transfromar assim:
    info
    ======
    1245
    45678
    7789
    64646
    4478

  5. Muito bom o post! Me ajudou bastante!
    No caso teria como classificar os grupos formado? Por exemplo tenho o seguinte caso, definir alimentação a partir da dieta, vamos dizer que exista a dieta 1 (Leve), 2 (moderada), 3 (controlada) e 4 (diabetes) as combinações das dietas recebem a seguinte classificação: Maçã (1,2), Pão (1,2,3), Café c/ adoçante (1,2,3,4). Para a a parte do agrupamento utilizei um dos exemplos mostrados no post, porem estou com dificuldades na classificação do grupo. Tentei usar um case e também o decode porem os mesmo só reconhecem o primeiro valor do grupo.

  6. Bom dia a todos.
    muito bom o post adorei mesmo me ajudou muito, só estou com um problemas e ainda não sei como resolver esta me retornando o seguinte erro.:

    ——————————————————————————–
    >>> ** AUT RODRIGO ANTONELLINE/CARLOS ALBERTO ANTONELLINE CLIENTE FOI ASSALTA
    DA SOMENTE LIBERAR PARA OS SOCIOS OU OS AUTORIZADOS **

    ERROR:
    ORA-01489: result of string concatenation is too long

    915 rows selected.

    SQL> SPOOL OFF

    E não sei ainda como resolver se alguém puder me ajudar.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *