Como alterar o formulário de tabela para converter linhas em colunas no MySQL

O MySQL é um dos sistemas de gerenciamento de banco de dados relacionais mais populares do mundo. O MySQL nos permite armazenar, gerenciar e analisar dados em forma de tabela. Uma tabela consiste em linhas e colunas que representam entidades e atributos de dados. No entanto, às vezes precisamos alterar a forma da tabela para converter linhas em colunas ou vice-versa. Isso é chamado de transposição de tabela.

As tabelas de transposição podem ser úteis por vários motivos, tais como:

• Simplifique a visualização e a compreensão de dados
• Otimizar o desempenho da consulta e do índice
• Adapte as estruturas de dados às necessidades dos negócios ou dos aplicativos
• Executar operações de agregação ou cálculo em dados

No entanto, o MySQL não tem uma função interna para transpor tabelas diretamente. Portanto, precisamos usar certas técnicas ou truques para alcançar esse objetivo. Neste artigo, discutiremos várias maneiras de alterar a forma de uma tabela para converter linhas em colunas no MySQL, juntamente com exemplos e explicações.

Método 1: Usando a função de agregação e a cláusula GROUP BY

Uma maneira de alterar a forma de uma tabela para converter linhas em colunas no MySQL é usar a função de agregação e a cláusula GROUP BY. Uma função de agregação é uma função que toma um conjunto de valores como entrada e retorna um único valor como saída. Algumas funções de agregação comumente usadas no MySQL são:

• SOMA(): Calcula o número total de um conjunto de valores
• AVG(): Calcula a média de um conjunto de valores
• MIN(): Devolve o menor valor de um conjunto de valores
• MAX(): Devolve o maior valor de um conjunto de valores
• COUNT(): Conta o número de valores presentes em um conjunto de valores

Uma cláusula GROUP BY é uma cláusula usada para agrupar linhas que têm o mesmo valor em uma ou mais colunas. A cláusula GROUP BY é normalmente usada em conjunto com uma função de agregação para calcular valores agregados para cada grupo.

Para alterar o formulário de tabela para converter linhas em colunas usando a função de agregação e a cláusula GROUP BY, precisamos executar as seguintes etapas:

• Especifique a coluna que será a nova linha na tabela de resultados. Esta coluna é geralmente uma coluna que tem valores diferentes e não muitos. Por exemplo, colunas de ano, mês, categoria, tipo, etc.
• Especifique a coluna que será a nova coluna na tabela de resultados. Essa coluna geralmente é uma coluna que tem o mesmo valor ou o valor limitado para cada linha. Por exemplo, colunas de nome, produto, marca, etc.
• Especifique a função de agregação que será usada para calcular o valor de cada nova combinação de linha e coluna. Essa função de agregação depende do tipo de dados e da finalidade de nossa análise. Por exemplo, SUM(), AVG(), MIN(), MAX(), etc.
• Crie uma consulta SQL que use a função de agregação e a cláusula GROUP BY para retornar a tabela de resultados desejada.

Vejamos um exemplo para entender melhor esse caminho. Suponha que tenhamos a seguinte tabela que armazena dados de vendas de produtos para cada mês e categoria:

Esta tabela tem três colunas: mês, categoria e vendas. Queremos alterar a forma dessa tabela para converter linhas em colunas, para que possamos ver o total de vendas para cada categoria e mês em uma tabela. A tabela de resultados que queremos é a seguinte:

Para atingir esse objetivo, podemos usar o caminho 1 com os seguintes passos:

• Especifique a coluna que será a nova linha na tabela de resultados. Nesse caso, a coluna de categoria será uma nova linha, pois tem valores diferentes e não muitos (A, B, C).
• Especifique a coluna que será a nova coluna na tabela de resultados. Nesse caso, a coluna mês será a nova coluna, pois tem o mesmo valor ou valor limitado para cada linha (jan, fev, mar).
• Especifique a função de agregação que será usada para calcular o valor de cada nova combinação de linha e coluna. Nesse caso, usaremos a função SOMA() para calcular o total de vendas para cada categoria e mês.
• Crie uma consulta SQL que use a função de agregação e a cláusula GROUP BY para retornar a tabela de resultados desejada. As consultas SQL que podemos usar são as seguintes:

SELECT
category
SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM
sales_table
GROUP BY
category;

Esta consulta faz o seguinte:

• Selecione a coluna de categoria na tabela sales_table
• Use as funções SOMA() e CASO para calcular o total de vendas para cada categoria e mês. CASE é uma instrução condicional que retorna um valor com base em uma determinada condição. Nesta consulta, usamos CASE para verificar o valor mensal e retornar o valor de venda, se apropriado, ou 0 se não. Por exemplo, SUM(CASE WHEN month = ‘Jan’ THEN sales ELSE 0 END) retornará o total de vendas de janeiro para cada categoria, ou 0 se não houver vendas para esse mês.
• Dê um nome de alias para cada nova coluna usando AS. Alias é um nome alternativo que podemos dar a uma coluna ou tabela para facilitar a escrita e a leitura de uma consulta. Nesta consulta, damos um nome de alias para cada nova coluna com um nome de mês correspondente, como Jan, Feb e Mar.
• Use a cláusula GROUP BY para agrupar linhas pela coluna de categoria. A cláusula GROUP BY cria uma linha para cada valor exclusivo na coluna de categoria e calcula um valor agregado para cada outra coluna.

Essa consulta irá gerar a tabela de resultados que desejamos, como mostrado acima.