Função LISTAGG

Para cada grupo em uma consulta, a função de agregação LISTAGG ordena as linhas desse grupo de acordo com a expressão ORDER BY e, depois, concatena os valores em uma única string.

Sintaxe

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ]   

Argumentos

DISTINCT

Uma cláusula que elimina valores duplicados da expressão especificada antes de concatenar. Os espaços à direita são ignorados. Por exemplo, as strings 'a' e 'a ' são tratadas como duplicatas. LISTAGG usa o primeiro valor encontrado. Para obter mais informações, consulte Significância de espaços em branco.

aggregate_expression

Qualquer expressão válida, como um nome de coluna, que forneça os valores para agregar. Valores NULL e strings vazias são ignoradas.

delimitador

A constante de string que separará os valores concatenados. O padrão é NULL.

WITHIN GROUP (ORDER BY order_list)

Uma cláusula que especifica a ordem de classificação dos valores agregados.

Retornos

VARCHAR(MAX). Se o resultado for maior que o tamanho máximo de VARCHAR, LISTAGG retornará o seguinte erro:

Invalid operation: Result size exceeds LISTAGG limit

Observações de uso

• Se uma instrução inclui várias funções LISTAGG que usam cláusulas WITHIN GROUP, todas as cláusulas WITHIN GROUP devem usar os mesmos valores ORDER BY.

  Por exemplo, a seguinte instrução retorna um erro.

  SELECT LISTAGG(sellerid) 
  WITHIN GROUP (ORDER BY dateid) AS sellers,
  LISTAGG(dateid) 
  WITHIN GROUP (ORDER BY sellerid) AS dates
  FROM sales;

  As instruções a seguir são executadas com êxito.

  SELECT LISTAGG(sellerid) 
  WITHIN GROUP (ORDER BY dateid) AS sellers,
  LISTAGG(dateid) 
  WITHIN GROUP (ORDER BY dateid) AS dates
  FROM sales;
  
  SELECT LISTAGG(sellerid) 
  WITHIN GROUP (ORDER BY dateid) AS sellers,
  LISTAGG(dateid) AS dates
  FROM sales;

Exemplos

O seguinte exemplo agrega os IDs de vendedor, ordenados por ID de vendedor.

SELECT LISTAGG(sellerid, ', ') 
WITHIN GROUP (ORDER BY sellerid) 
FROM sales
WHERE eventid = 4337;

listagg                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
380, 380, 1178, 1178, 1178, 2731, 8117, 12905, 32043, 32043, 32043, 32432, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 47188, 48294

O exemplo a seguir usa DISTINCT para retornar uma lista de IDs de vendedor exclusivos.

SELECT LISTAGG(DISTINCT sellerid, ', ') 
WITHIN GROUP (ORDER BY sellerid) 
FROM sales
WHERE eventid = 4337;

listagg                                                                                    
-------------------------------------------------------------------------------------------
380, 1178, 2731, 8117, 12905, 32043, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 48294

O seguinte exemplo agrega os IDs de vendedor por ordem de data.

SELECT LISTAGG(sellerid, ', ')  
WITHIN GROUP (ORDER BY dateid) 
FROM sales
WHERE eventid = 4337;

   listagg
-----------------------------------------------------------------------------------------------------------------------------------------
 41498, 47188, 47188, 1178, 1178, 1178, 380, 45676, 46324, 48294, 32043, 32043, 32432, 12905, 8117, 38750, 2731, 32432, 32043, 380, 38669

O exemplo a seguir retorna uma lista separada por pipes das datas de vendas do comprador com ID 660.

SELECT LISTAGG(
    (SELECT caldate FROM date WHERE date.dateid=sales.dateid), ' | '    
)
WITHIN GROUP (ORDER BY sellerid DESC, salesid ASC)
FROM sales
WHERE buyerid = 660;

             listagg
-------------------------------------------------
2008-07-16 | 2008-07-09 | 2008-01-01 | 2008-10-26

O exemplo a seguir retorna uma lista separada por vírgulas dos IDs de venda dos compradores com ID 660, 661 e 662.

SELECT buyerid, 
LISTAGG(salesid,', ')
WITHIN GROUP (ORDER BY salesid) AS sales_id
FROM sales
WHERE buyerid BETWEEN 660 AND 662
GROUP BY buyerid
ORDER BY buyerid;
            
buyerid |                sales_id
--------+-----------------------------------------------------
660     | 32872, 33095, 33514, 34548
661     | 19951, 20517, 21695, 21931
662     | 3318, 3823, 4215, 51980, 53202, 55908, 57832, 171603