Como agrupar vários registros em uma única coluna com SQL

Como agrupar vários registros em uma única coluna com SQL

Neste artigo vamos ver como agrupar mais de uma linha de resultados numa mesma coluna. Vamos ver exemplos utilizando o MySQL e o SQL Server.

Vamos criar uma tabela para armazenar os dados que vamos usar em nossa consulta

 

MySQL

CREATE TABLE vendas (

    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

    periodo DATETIME,

    categoria VARCHAR(50),

    quantidade INT

);

SQL Server

CREATE TABLE vendas (

    id int IDENTITY(1,1) PRIMARY KEY,

    periodo DATETIME,

    categoria VARCHAR(50),

    quantidade INT

);

 

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-06-01', 'ELETRONICOS', 100);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-06-01', 'MOVEIS', 200);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-06-01', 'VESTUARIO', 700);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-07-01', 'ELETRONICOS', 700);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-07-01', 'ALIMENTACAO', 500);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-07-01', 'VESTUARIO', 300);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-08-01', 'FERRAMENTAS', 320);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-08-01', 'MOVEIS', 170);

INSERT INTO vendas (periodo, categoria, quantidade) VALUES ('2022-08-01', 'VESTUARIO', 500);

 

Perceba que em nossos registros temos várias categorias por mês. Nosso objetivo e criar uma consulta agrupada por periodo onde as categorias do mês ocupem apenas uma coluna. Também queremos somar todas as quantidades daquele mês.

Se não houvesse a necessidade de exibir as categorias a solução seria bem simples

 

SELECT periodo, sum(quantidade) as total from vendas GROUP BY periodo ORDER BY periodo

 

Como agrupar as categorias numa única coluna?

 

No MySQL podemos utilizar a função GROUP_CONCAT

 

SELECT periodo, GROUP_CONCAT(categoria) as categorias, sum(quantidade) as total from vendas GROUP BY periodo ORDER BY periodo

 

Por padrão, os resultados agrupados são separados por virgula. Mas você pode mudar o separador ou até colocar os resultados em ordem utilizando outros parâmetros que a função GROUP CONCAT suporta

 

SELECT periodo, GROUP_CONCAT(DISTINCT categoria ORDER BY categoria  SEPARATOR', ') as categorias, sum(quantidade) as total from vendas GROUP BY periodo ORDER BY periodo

 

Já no SQL Server, podemos usar a função STRING_AGG disponveil a partir da versão 2017 do SQL Server.

 

SELECT periodo,STRING_AGG(categoria,',') as categorias, sum(quantidade) as total from vendas GROUP BY periodo ORDER BY periodo

 

Da mesma forma que o GROUP_CONCAT, esta função permite ordenar os resultados. Para isso utilizamos WITHIN GROUP após a função STRING_AGG para informar a forma de ordenação do resultado.

 

SELECT periodo,STRING_AGG(categoria,',')  WITHIN GROUP (ORDER BY categoria) as categorias, sum(quantidade) as total from vendas GROUP BY periodo ORDER BY periodo

 

Para versões anteriores do SQL Server o processo é mais complexo, você precisa utilizar STUFF e XML PATH.

A função STUFF insere uma cadeia de caracteres dentro de outra cadeia de caracteres. Sua sintaxe é 

 

STUFF ( string , posição inicial , tamanho ,segunda_string )

 

Nossa consulta vai ficar da seguinte forma:

 

SELECT periodo,STUFF((SELECT ', ' + CONVERT(VARCHAR, categoria)  FROM vendas V

                    WHERE V.periodo = T.periodo ORDER BY categoria

                    FOR XML PATH('')), 1, 2, '') as categorias, sum(quantidade) as total from vendas as T GROUP BY periodo ORDER BY periodo

 

Acrescentando FOR XML PATH no final da consulta que utilizamos como primeiro parâmetro da função STUFF permite retornar o conteúdo desta consulta como elementos XML, com o nome do elemento contido dentro do argumento da função PATH

Vamos executar novamente a nossa consulta mas desta vez sem a função STUFF para entender melhor o retorno

 

SELECT periodo,(SELECT ', ' + CONVERT(VARCHAR, categoria)  FROM vendas V

                    WHERE V.periodo = T.periodo ORDER BY categoria

                    FOR XML PATH('')) as categorias, sum(quantidade) as total from vendas as T GROUP BY periodo ORDER BY periodo

 

O retorno da primeira linha foi:

 

, ELETRONICOS, MOVEIS, VESTUARIO

 

A função do STUFF em nosso exemplo foi apenas para remover os dois primeiros caracteres dos resultados, que são uma virgula e um espaço em branco.

 

Reforçando os parâmetros que foram utilizados na função STUFF

 

1 - A string de resultados, extraída da consulta informada, resultados separados com vírgula antes de cada valor.

2 - A posição para iniciar a exclusão de caracteres para inserir um novo valor (1, vamos adicionar em seu lugar um valor em branco)

3 - O número de caracteres para excluir (2, virgula inicial e espaço)

4 - Uma string vazia, que é o valor que vamos adicionar.

 

Outros conteudos que podem ser de seu interesse

pesquisa fonetica no SQL
26/04/2020SQL

pesquisa fonetica no SQL

Veja como pesquisar informações por semelhança fonética no MySQL e no SQL Server

Saiba mais...
Como localizar queries lentas no SQL Server
23/09/2019SQL

Como localizar queries lentas no SQL Server

Aprenda como localizar as queries que deixam o seu banco de dados lento

Saiba mais...

Conteúdo sobre banco de dados sem complicação!