Convertendo linhas em colunas usando MySQL

Convertendo linhas em colunas usando MySQL

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

 

Encerramento

Neste artigo você viu como é possível transformar linhas de resultados em colunas com o MySQL. 

Uma outro recurso muito comum é converter varias linhas de resultados em uma única coluna. Isso é utilizado quando queremos por exemplo agrupar categorias e exibir numa única coluna as subcategorias relacionadas

Se você quer aprender como fazer isso, não deixe de conferir o link a seguir:

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

 

Outros conteudos que podem ser de seu interesse

Colunas Calculadas no SQL Server
27/10/2019SQL

Colunas Calculadas no SQL Server

Veja como criar colunas calculadas no SQL Server

Saiba mais...
Blogs e tutoriais para estudar data science
04/12/2022SQL

Blogs e tutoriais para estudar data science

Uma lista de sites para você aprofundar seus conhecimentos de Data Science

Saiba mais...

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