Convertendo linhas em colunas usando MySQL

Aprenda a transformar linhas em colunas no 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.

 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

 

 

Quer conferir mais dicas sobre banco de dados? Clique aqui e veja mais dicas!

Veja outros conteúdos que podem ser de seu interesse