SQL

Convertendo linhas em colunas usando MySQL

Aprenda a transformar linhas em colunas no MySQL

13/09/2020SQL

Durante o trabalho de análise de dados, sempre se faz necessário converter os dados de modo a atender as necessidades de nossos usuários. Uma forma de conversão muito útil é fazer o agrupamento de dados exibidos em linha, para que cada elemento distinto da linha seja transformado numa coluna. Vamos ver como transformar linhas em colunas no MySQL

 Para acompanhar este artigo, vamos primeiro criar uma tabela de testes e carregar com as informações que vamos utilizar.

 CREATE TABLE IF NOT EXISTS minha_tabela (

  id int(11) NOT NULL,

  data datetime NOT NULL,

  departamento varchar(50) NOT NULL,

  tipo_despesa varchar(50) NOT NULL,

  valor decimal(10,0) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

 

 Os dados que vamos utilizar serão os seguintes

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-04', 'COMPRAS', 'CADEIRA', 200);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-06', 'MKT', 'VENTILADOR', 100);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-08', 'EXPEDICAO', 'MATERIAL ESCRITORIO', 300);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-10', 'TI', 'COMPUTADOR', 1200);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-15', 'COMPRAS', 'MESA', 300);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-17', 'MKT', 'CORTINA', 100);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-18', 'MKT', 'TAPETE', 100);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-18', 'EXPEDICAO', 'TELEFONE', 80);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-01-21', 'TI', 'CABO DE REDE', 300);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-02-01', 'COMPRAS', 'IMPRESSORA', 500);

INSERT INTO minha_tabela (data, departamento, tipo_despesa, valor) VALUES ('2017-02-01', 'MKT', 'TV', 1200);

 

Após carregar os dados acima, a sua tabela deve ter o seguinte conteúdo:

 

 

Nosso objetivo é fazer a seguinte análise, criar uma coluna para cada departamento, de modo a mostrar o total gasto no mês de janeiro. O resultado final será transformar linhas em colunas.

Se fizermos um agrupamento por departamento, ordenando por nome do departamento 

SELECT departamento, sum(valor) as valor from minha_tabela group by departamento order by departamento

Vamos ter o seguinte resultado:

 

Para poder inverter as linhas em colunas, vamos primeiro criar uma consulta de referencia. Neste exemplo, vamos pegar os registros de apenas 1 mês:

SELECT

        CASE WHEN departamento = 'MKT' THEN SUM(valor) ELSE 0 END AS MKT,

        CASE WHEN departamento = 'TI' THEN SUM(valor) ELSE 0 END AS TI,

        CASE WHEN departamento = 'COMPRAS' THEN SUM(valor) ELSE 0 END AS COMPRAS,

        CASE WHEN departamento = 'EXPEDICAO' THEN SUM(valor) ELSE 0 END AS EXPEDICAO

    FROM minha_tabela

    where month(data) = 1 and year(data) = 2017 

     GROUP BY departamento

 

No exemplo acima, fazemos uma consulta onde desejamos trabalhar com as colunas departamento e valor que atendam a condição de ser do mês de janeiro. Sobre este resultado, aplicamos uma função de agrupamento para a coluna "valor", a função escolhida foi a SUM, desta forma somamos todos os valores. Essa consulta retorna uma linha para cada departamento. 

Para unificar os departamentos em apenas 1 linha, vamos utilizar uma segunda consulta, que vai somar os registros gerados por esta primeira consulta.

SELECT 

    SUM(MKT) AS MKT,

    SUM(TI) AS TI,

    SUM(COMPRAS) AS COMPRAS,

    SUM(EXPEDICAO) AS EXPEDICAO

FROM (

SELECT

        CASE WHEN departamento = 'MKT' THEN SUM(valor) ELSE 0 END AS MKT,

        CASE WHEN departamento = 'TI' THEN SUM(valor) ELSE 0 END AS TI,

        CASE WHEN departamento = 'COMPRAS' THEN SUM(valor) ELSE 0 END AS COMPRAS,

        CASE WHEN departamento = 'EXPEDICAO' THEN SUM(valor) ELSE 0 END AS EXPEDICAO

    FROM minha_tabela

    where month(data) = 1 and year(data) = 2017 

     GROUP BY departamento

) resultado

 

O retorno desta consulta seria o seguinte:

 

 Se você quiser saber a quantidade de compras ao invés do valor, basta fazer o seguinte ajuste:

  

SELECT 

    SUM(MKT) AS MKT,

    SUM(TI) AS TI,

    SUM(COMPRAS) AS COMPRAS,

    SUM(EXPEDICAO) AS EXPEDICAO

FROM (

SELECT

        CASE WHEN departamento = 'MKT' THEN COUNT(valor) ELSE 0 END AS MKT,

        CASE WHEN departamento = 'TI' THEN COUNT(valor) ELSE 0 END AS TI,

        CASE WHEN departamento = 'COMPRAS' THEN COUNT(valor) ELSE 0 END AS COMPRAS,

        CASE WHEN departamento = 'EXPEDICAO' THEN COUNT(valor) ELSE 0 END AS EXPEDICAO

    FROM minha_tabela

    where month(data) = 1 and year(data) = 2017 

     GROUP BY departamento

) resultado 

 

E o resultado seria:

 

Se você também trabalha com o banco de dados SQL Server e deseja ver como implementar este tipo de consulta nele, você pode consultar o seguinte artigo:

 Convertendo linhas em colunas no SQL Server usando PIVOT

 

Outros conteudos que podem ser de seu interesse

Validando emails com o SQL Server
13/10/2019SQL

Validando emails com o SQL Server

Aprenda a validar e-mails utilizando o SQL Server

Saiba mais...
Importar arquivos CSV com o MySQL
30/09/2019SQL

Importar arquivos CSV com o MySQL

Aprenda a importar arquivos CSV utilizando o MySQL

Saiba mais...

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

MySQL - Referência Rápida

 

SQL Server - Referência Rápida

 

SQL vs Mongo